Add common prefix to all cells in Excel

Question

I have a column with some text in each cell.
I want to add some text, for example "X", at the start of all cells. For example:

A             B
-----  >>>>  ----
1            X1
2            X2
3            X3

What is the easiest way to do this?

Answer 1

Type this in cell B1, and copy down...

="X"&A1

This would also work:

=CONCATENATE("X",A1)

And here's one of many ways to do this in VBA (Disclaimer: I don't code in VBA very often!):

Sub AddX()
    Dim i As Long

    With ActiveSheet
    For i = 1 To .Range("A65536").End(xlUp).Row Step 1
        .Cells(i, 2).Value = "X" & Trim(Str(.Cells(i, 1).Value))
    Next i
    End With
End Sub

Answer 2

Michael.. if its just for formatting then you can format the cell to append any value.

Just right click and select Format Cell on the context menu, select custom and then specify type as you wish... for above example it would be X0. Here 'X' is the prefix and 0 is the numeric after.

Hope this helps..

Cheers...