adding a text prefix to an entire column

[Sveb]

I have a column of 500+ item numbers that I'd like to add
the text prefix "MR-" to. Is there any way to format the
entire column or do I have to go to each cell and type my
text prefix "MR-"? If I can't append a prefix, could I
create a column of 500 "MR-" entries and then merge the 2
columns. I'm at a loss. Thanks in advnace for any help.

 

[Dave R.]

Try using a helper column, next to the column of numbers. This is if you
want to "add the text prefix" - "formatting" is something else as you
probably know.

Just use ="MR-"&A1
then copy it down. select all the new MR-s and copy then go to the original
location, paste special>values.

 

[Gord Dibben]

Sveb

Assuming data in column A

In B1 enter ="MR-" & A1

Double-click on the fill-handle(small black square at bottom right of B1) to
fill down.

When happy, copy column B and paste special(in place)>Values>OK>Esc then
delete column A.

Macro solution..........

Sub Add_Text()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
whichside = InputBox("Left = 1 or Right =2")
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
If whichside = 1 Then
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Else
For Each cell In thisrng
cell.Value = cell.Value & moretext
Next
End If
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

Gord Dibben Excel MVP