Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Minnesota, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!

    Thanks in advance,
    Karnie Moesenthin
    Technical Training and Development Professional
    Legal Industry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Minnesota, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Karnie Moesenthin
    Technical Training and Development Professional
    Legal Industry

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Minnesota, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Karnie Moesenthin
    Technical Training and Development Professional
    Legal Industry

Posting Permissions

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