Insert a letter in front of number in every cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

grativo@aol.com - 06 Oct 2007 19:49 GMT

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?

Reply to this Message

David Biddulph - 06 Oct 2007 19:56 GMT

="B"&A1 and copy down.

Signature

David Biddulph

>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?

Reply to this Message

T. Valko - 06 Oct 2007 21:22 GMT

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

Signature

Biff
Microsoft Excel MVP

Reply to this Message

Darren Bartrup - 08 Oct 2007 14:50 GMT

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.

> You can do it in one operation with a macro.
>
[quoted text clipped - 26 lines]
> > would like to change the cell to B2350. How do I do this without
> > manually changing the data on every cell?

Reply to this Message