Append same text to every cell in a column in Excel

Question

How can I append text to every cell in a column in Excel? I need to add a comma (",") to the end.

Example:

[email protected] turns into [email protected],

Data Sample:

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

Embryotomy

Answer 1

See if this works for you.

• All your data is in column A (beginning at row 1).
• In column B, row 1, enter =A1&","
• This will make cell B1 equal A1 with a comma appended.
• Now select cell B1 and drag from the bottom right of cell down through all your rows (this copies the formula and uses the corresponding column A value.)
• Select the newly appended data, copy it and paste it where you need using Paste -> By Value

That's It!

Whack

The last step description seems incomplete. I had to actually copy cell B1, then select the rest of the cells below it, and then paste. – Function

Then, for anyone who doesn't know, you can copy and Paste Special... > Values to move the resulting values back to your original column without the formula destroying them. – Alberta

What if you want to remove something? – Kraigkrait

Answer 2

It's a simple "&" function.

=cell&"yourtexthere"

Example - your cell says Mickey, and you want Mickey Mouse. Mickey is in A2. In B2, type

=A2&" Mouse"

Then, copy and "paste special" for values.

B2 now reads "Mickey Mouse"

Occupy

Answer 3

It's simple...

=CONCATENATE(A1, ",")

Example: if [email protected] is in the A1 cell then write in another cell: =CONCATENATE(A1, ",")

[email protected] After this formula you will get [email protected],

For remove formula: copy that cell and use Alt + E + S + V or paste special value.

Sorrells

Answer 4

There is no need to use extra columns or VBA if you only want to add the character for display purposes.

As this post suggests, all you need to do is:

1. Select the cell(s) you would like to apply the formatting to
2. Click on the Home tab
3. Click on Number
4. Select Custom
5. In the Type text box, enter your desired formatting by placing the number zero inside whatever characters you want.

Example of such text for formatting:

• If you want the cell holding value 120.00 to read $120K, type $0K

Pummel

Answer 5

Pretty simple...you could put all of them in a cell using the concatenate function:

=CONCATENATE(A1, ", ", A2, ", ", and so on)

Merciful

Answer 6

Highlight the column and then Ctrl + F.

Find and replace

Find ".com"

Replace ".com, "

And then one for .in

Find and replace

Find ".in"

Replace ".in, "

Furlana

Answer 7

Simplest of them all is to use the "Flash Fill" option under the "Data" tab.

1. Keep the original input column on the left (say column A) and just add a blank column on the right of it (say column B, this new column will be treated as output).

2. Just fill in a couple of cells of Column B with actual expected output. In this case:

         [email protected],
         [email protected],
   

3. Then select the column range where you want the output along with the first couple of cells you filled manually ... then do the magic...click on "Flash Fill".

It basically understands the output pattern corresponding to the input and fills the empty cells.

Serilda

Answer 8

Select the range of cells, type in the value and press Ctrl + Enter.

This, of course, is true if you want to do it manually.

Parlin

Answer 9

I just wrote this for another answer:

You would call it using the form using your example: appendTextToRange "[theRange]", ",".

Sub testit()
    appendTextToRange "A1:D4000", "hey there"
End Sub


Sub appendTextToRange(rngAddress As String, append As String)

    Dim arr() As Variant, c As Variant
    arr = Range(rngAddress).Formula

    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            Debug.Print arr(x, y)
            If arr(x, y) = "" Then
                arr(x, y) = append
            ElseIf Left(arr(x, y), 1) = "=" Then
                arr(x, y) = arr(x, y) & " & "" " & append & """"
            Else
                arr(x, y) = arr(x, y) & " " & append
            End If
        Next
    Next
    Range(rngAddress).Formula = arr

End Sub

Submerge

Answer 10

Put the text/value in the first cell, then copy the cell, mark the whole colum and 'paste' the copied text/value.

This works in Excel 97 - sorry no other version available on my side...

Chronologist

Answer 11

This is addition to @Edward-Leno 's answer for more detail/explanation and cases where the text cells are formulas instead of values, and you want to retain the original formula.

Suppose your cells look like this (formulas)

="email" & "@" & "address.com"

=A1 & "@" & C1

instead of this (values)

[email protected]

If "email" and "address.com" were some cells like A1 is the email and C1 is the address.com part, then you'd have something like =A1&"@"&C1 which would be important to retain since A1 and C1 might not be constants and can change, so the comma-concatenated values would change, like if C1 is "gmail.com", "yahoo.com", or something else based on its formula.

Values method: The following steps will successfully append text but only keep the value using a scratch column (this works for rows, too, but for simplicity, the directions are for columns)

1. Assume column A is your data.
2. In scratch column B, start anywhere like the top of column B such as at B1 and put this formula:

=A1&","

Essentially, the "&" is the concatenation operator, combining two strings together (numbers are converted to strings). The "," can be adjusted to ", " if you want a space after the comma.

3. Copy the cell B1 and copy it down to all other cells in column B, either by clicking at the bottom right of cell B1 and dragging down, or copying with "Ctrl+C" or right-click > "Copy".

4. Paste B1 to all cells in column B with "Ctrl+V" or right-click > "Paste Options:" > "Paste". You should see the data looking like you intended.

5. Copy all cells in column B and paste them to where you want via right-click > "Paste Options:" > "Values". We select values so it doesn't mess up any formatting or conditional formatting

Formula retention method: The following steps will successfully retain the original formula. The process is similar to the values method, and only step 2, the formula used to concatenate the comma, changes.

1. Assume column A is your data.
2. In scratch column B, start anywhere like the top of column B such as at B1 and put this formula:

=FORMULATEXT(A1)&","

FORMULATEXT() grabs the formula of the cell as opposed to the value of it, so a simple example would be that it grabs =2+2 instead of 4, or =A1 & "@" & C1 where A1 is "Bob" and C1 is "gmail.com" instead of [email protected].

Note: This formula only works for Excel versions 2013 and greater. For alternative equivalent solutions for Excel 2010 and older, see this superuser answer: https://superuser.com/a/894441/495155

3. Copy the cell B1 and copy it down to all other cells in column B, either by clicking at the bottom right of cell B1 and dragging down, or copying with "Ctrl+C" or right-click > "Copy".

4. Paste B1 to all cells in column B with "Ctrl+V" or right-click > "Paste Options:" > "Paste". You should see the data looking like you intended.

5. Copy all cells in column B and paste them to where you want via right-click > "Paste Options:" > "Values". We select values so it doesn't mess up any formatting or conditional formatting

Zymosis

Answer 12

Type it in one cell, copy that cell, select all the cells you want to fill, and paste.

Alternatively, type it in one cell, select the black square in the bottom-right of that cell, and drag down.

Coagulate