Append same text to every cell in a column in Excel
ExcelExcel 2007Excel Problem Overview
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:
m2engineers@yahoo.co.in
satishmm_2sptc@yahoo.co.in
threed_precisions@rediffmail.com
workplace_solution@yahoo.co.in
threebworkplace@dataone.in
dtechbng@yahoo.co.in
innovations@yahoo.co.in
sagar@mmm.com
bpsiva@mmm.com
nsrinivasrao@mmm.com
pdilip@mmm.com
vvijaykrishnan@mmm.com
mrdevaraj@mmm.com
b3minvestorhelpdesk@mmm.com
sbshridhar@mmm.com
balaji@mmm.com
schakravarthi@mmm.com
srahul1@mmm.com
khramesh2@mmm.com
avinayak@mmm.com
rockindia@hotmail.com
Excel Solutions
Solution 1 - Excel
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!
Solution 2 - Excel
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"
Solution 3 - Excel
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.
Solution 4 - Excel
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:
- Select the cell(s) you would like to apply the formatting to
- Click on the
Home
tab - Click on
Number
- Select
Custom
- 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
Solution 5 - Excel
Pretty simple...you could put all of them in a cell using the concatenate function:
=CONCATENATE(A1, ", ", A2, ", ", and so on)
Solution 6 - Excel
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, "
Solution 7 - Excel
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.
Solution 8 - Excel
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
Solution 9 - Excel
Simplest of them all is to use the "Flash Fill" option under the "Data" tab.
-
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).
-
Just fill in a couple of cells of Column B with actual expected output. In this case:
m2engineers@yahoo.co.in, satishmm_2sptc@yahoo.co.in,
-
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.
Solution 10 - Excel
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...
Solution 11 - Excel
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.