Seach/Replace Help (2k/2003)

Topic

New Reply

Hello y’all,

I have a column of numbers (actually about a thousand rows of them):
15101
15102
20301
51612

I want to add the letter B before each number. The first approach I took was simply to create a custom number format: “B”0 and apply it to the column. That works, in that the letter B is viewed (and printed) before each number.

However, can anyone help with how to quickly insert the B as part of the cell content before the number in each cell? I am thinking a variation of Search and Replace using wildcards would work, but I am looking for what I need in the “Replace” textbox. I am looking for a solution that does not use a macro as the approach of choice.

Thanks so much,
Rich

ps. It is so nice to see our forum back up and running! Thanks so very much.

Reply | Quote

Replies

Find / Replace will not work here.

Let’s say that the values are in A1:A100.
If necessary, insert a blank column in column B.
In B1, enter the formula ="B"&A1
Fill down as far as needed, for example by double clicking the fill handle (the little black square in the lower right corner) of B1.
With B1:B100 still selected, select Edit | Copy or press Ctrl+C.
Click in cell A1.
Select Edit | Paste Special…
Click on Values, then OK.
You have now prefixed the original values with “B”.
You can delete or clear column B, it is no longer needed.

Reply | Quote

Dear Hans,

But, of course! I knew you’d think of such an easy way!

Thanks so much for pointing me in that direction! It was exactly what the doctor needed! It worked perfectly!

Blessings,
Rich

Reply | Quote

You could Select the column and apply a custom format.
Select the column in question
Select Format Cells
On the nuumber tab select the Custom Category
In the type area use something like “B” 00000

Carla

Reply | Quote