Insert a letter in front of number in every cell?

  • Thread starter Thread starter grativo
  • Start date Start date
G

grativo

I have a whole column of numbers which I would like to add a letter to
in the beginning. For example, if the cell has the number 2350, I
would like to change the cell to B2350. How do I do this without
manually changing the data on every cell?
 
You can do it in one operation with a macro.

Hit ALT F11 to open the VB editor
Hit CTRL R to open the project exlporer
Locate your file name in the explorer pane
Select the file name and right click
Select Insert>Module
Paste the code below into the window that opens on the right:

Sub AddB()

Dim cell As Range
For Each cell In Selection
If cell.Value <> "" Then
cell.Value = "B" & cell.Value
Else: cell.Value = cell.Value
End If
Next cell
End Sub

Close the VB editor to return to Excel

Select the range of cells of interest
Goto Tools>Macro>Macros
Select the AddB Macro and click Run
 
If you want the number to stay as a number you could give the cell a custom
number format.
[>1000]"B"#;[<=1000]"C"#;General

Anything over 1000 will be prefixed with a B, anything under or equal to
1000 will be prefixed with a C.
 
Back
Top