Is there a way????? 2

[susanh]

Hi there,
Is there a quick way to insert for example the letter "B" before a set of numbers in a cell.

For example
123456

I want the data to reflect
B123456


I have a huge column of numbers that I want the letter B in front of and don't want to have to do it manually.

 

[al99337]

Col A is filled with B's
Col B is your column of numbers
C1 =A1&B1
copy C1 to rest of col C

Have fun
alj

 

[NickSleeman]

put this formula in a blank column

="b" & A1

where A1 is the cell where the first number is
Drag the formula all the way down to the last row of your data by highlighting the cell and dragging down the bottom right hand corner. Cut and paste special values over your original numbers

hope this helps

 

[Jovix]

Hi guys, please allow me to jump in here. What if it was reversed? What if the b was in the column and I wanted to remove it, how would that work?

G

 

[KenWright]

Assuming the B was the first letter in each, select the column, and do Data / text To Columns / Fixed Width / Set a break after the first letter and hit OK. If you don't want the B at all then hit 'Do Not import this column' in the wizard.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jfhewitt]

Any reason why you can't format the column with a custom number B######?

 

[susanh]

Hi there,
Actually this suggestion from Nick worked best.

put this formula in a blank column

="b" & A1

where A1 is the cell where the first number is
Drag the formula all the way down to the last row of your data by highlighting the cell and dragging down the bottom right hand corner. Cut and paste special values over your original numbers

hope this helps

Thanks for all of your help.

 

[pete1001]

If you need to just see the "B" but not use it, a quicker way would be to:

Highlight the column
Format... Cells... Number Tab... 'Custom'
Change the custom format to: "B"#

Pete

 

[DeLaMartre]

Good one, Ken!

That is (at least to me...) a novel approach.

Thanks,

-Bob in California