Prefix macro

[TedL]

I deal with the same problem all the time. I would like to create
macro for a colum of data that would allow me to insert a custom
character text prefix to the data.
For example in Col A the cells are populated with a Part Number i
cell A1= 100-600125, cell A2 = 500-235487, cell A3 = 547-255684, etc.

I would like to add a prefix to all of the cells in the colum ie "PRE-
so that the results would be each cell has the prefix added to it, i
cell A1 = PRE-100-600125, cell A2 = PRE-500-235487, etc.

I hope this is clear. Thanks

 

[TedL]

I figured it out. Thank

 

[TedL]

Sub insertprefix()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim cell As Range
Dim myPrefix As String
myPrefix = "'"
myPrefix = InputBox("Supply prefix character(s)", "Supply prefix"
myPrefix)
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If Len(Trim(cell)) > 0 Then _
cell.Formula = myPrefix & cell.Text
Next cell
Application.Calculation = xlAutomatic 'xlCalculationAutomatic
Application.ScreenUpdating = False
End Su

 

[Gord Dibben]

Ted

Here's a generic macro that allows you to choose to add inputted text left or
right of existing data.

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