Insert a letter in front of number in every cell?
 

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?

Answer: Insert a letter in front of number in every cell?
 

1. Select the column of numbers that you want to modify.
2. Right-click on the selected cells and choose "Format Cells" from the drop-down menu.
3. In the "Format Cells" dialog box, select the "Custom" category.
4. In the "Type" field, enter the following format:
   Code:

   ---

   "B0"

   ---

   .
5. Click "OK" to apply the format to the selected cells.

This will add the letter "B" in front of every number in the selected column. The "0" in the format ensures that any leading zeros in the original number are preserved.

Insert a letter in front of number in every cell?
 

="B"&A1 and copy down.
--
David Biddulph

wrote in message
ups.com...
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?

Insert a letter in front of number in 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 InsertModule
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 ToolsMacroMacros
Select the AddB Macro and click Run



--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
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?

Insert a letter in front of number in every cell?
 

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.

"T. Valko" wrote:

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 InsertModule
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 ToolsMacroMacros
Select the AddB Macro and click Run



--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
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?