• Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

    Author
    Topic
    WSkarniem
    AskWoody Lounger
    January 30, 2006 at 4:36 pm #428867

    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,

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      January 30, 2006 at 4:44 pm #997349

      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.

      Reply | Quote
      • WSkarniem
        AskWoody Lounger
        January 30, 2006 at 4:56 pm #997355

        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?

        Reply | Quote
        • WSsdckapr
          AskWoody Lounger
          January 30, 2006 at 5:38 pm #997363

          Your indictation was “BWCXXXXXX” which means you should have “BWC000001″ and not “BWC00001” If there are only 5 digits not 6 use:

          ="BWC0" & RIGHT(A4,5)

          Steve

          Reply | Quote
          • WSkarniem
            AskWoody Lounger
            January 30, 2006 at 6:26 pm #997377

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

            Reply | Quote
    Viewing 0 reply threads
    Reply To: Insert leading 0 between text and numbers in a col (Excel 2003 sp1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.