Append same text to every cell in a column in Excel
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]
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!
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"
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.
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
Hometab - Click on
Number - Select
Custom - In the
Typetext 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.00to read$120K, type$0K