# Thread: Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

1. ## Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

One of our users has a spreadsheet with two columns, Beginning number and Ending number. The "numbers" all begin with BWCXXXXXX. The list has grown to more than 99,999 items, and, of course, when she moves to seven digits for the new numbers, the list won't sort properly. I'm sure there's some way to easily insert a leading 0 in all of the existing numbers, but my Excel skills aren't up to that. I'd appreciate any suggestions you may have. So would my paralegal!

2. ## Re: Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

Say your "numbers" are in A1:A100 (just as an example).
If necessary, insert a new column in column B.
In B1, enter this formula: ="BWC0" & RIGHT(A1,6)
Fill down to the end of the list (B100 in this example).
With B1:B100 still selected, copy it to the clipboard.
Select A1.
Then select Edit | Paste Special, and select the Values option. Click OK.
You can now delete column B.

3. ## Re: Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

Hans,

Thanks for the quick response. I'm apparently doing something wrong. My data starts in A4 with BWC00001, so in the new column B I entered ="BWC0" & RIGHT(A4,6) - I tried both with and without spaces between the " and & and the & and RIGHT. The result I get is BWC0C00001. In other words, I have a 0 C instead of just an additional 0. What am I doing wrong?

4. ## Re: Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

Your indictation was "BWCXXXXXX" which means you should have "BWC<font color=red>0</font color=red>00001" and not "BWC00001" If there are only 5 digits not 6 use:
<pre>="BWC0" & RIGHT(A4,5)</pre>

Steve

5. ## Re: Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

Thank you, thank you, both of you. That did it. I do love this lounge.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•