Announcement

Collapse
No announcement yet.

Add Missing Leading Zeros To Text

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Add Missing Leading Zeros To Text

    Hi All,

    I have a spreadsheet full of site codes which should be the format:
    AA1111

    or

    A1111

    Where A = any alpha character & 1 = any numeric character

    unfortunately the staff entering the code nearly always miss leading zeros off the numeric part of the site code.

    At the moment I manually correct this. Is there a better way to do this?

    Thanks,

    Pete
    Last edited by Dave Hawley; August 7th, 2008, 18:04.

  • #2
    Re: Check String And Insert 0's Until String Is Correct Length


    Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
     
     =IF(LEFT(A1)<>"0","0"&A1,A1) 

    Comment


    • #3
      Re: Add Missing Leading Zeros To Text

      Is there just one missing leading zero? If Dave's given you what you need then cool, otherwise let us know what values are entered and what values you want to convert them to.

      Thanks

      Welcome BTW!

      Comment


      • #4
        Re: Add Missing Leading Zeros To Text

        Hi Dave,

        Thanks for the reply. It appears to add 0's to the front of the site code so:
        AA1111
        becomes
        0AA1111
        What I was looking for was a way to check the number of numeric characters and if it is less then 4 add 0s after the Alpha characters until the number of numeric characters was equal to 4.

        so
        A123
        AA11
        becomes
        A0123
        AA0011

        Sorry if this wasn't clear.

        Thanks,

        Pete
        Last edited by Rangerst; August 7th, 2008, 21:21.

        Comment


        • #5
          Re: Add Missing Leading Zeros To Text

          Please remove all those code tags from your TEXT.

          Comment


          • #6
            Re: Add Missing Leading Zeros To Text

            Hi,

            See attached. This is a molestation of the code from here:
            http://office.microsoft.com/en-us/ex...CL100570551033

            It uses an array formula (in column B) to return the position of the first number in the string being looked at. The rest is pretty basic and could easily be concatenated into one column (but broken out to show construction more clearly).

            What do you think?
            Attached Files

            Comment


            • #7
              Re: Add Missing Leading Zeros To Text

              Hi,

              In C1,

              =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1

              In B1

              =LEFT(A1,C1)&TEXT(RIGHT(A1,LEN(A1)-C1),"0000")

              HTH
              Kris

              ExcelFox

              Comment


              • #8
                Re: Add Missing Leading Zeros To Text

                Kris, that's really smart!

                I was trying to solve something a bit different (and now, obviously, wrong!). In any case, FWIW I added this to yours in case the final string should always be 6 characters long:

                =LEFT(A1,C1)&TEXT(RIGHT(A1,LEN(A1)-C1),REPT("0",6-LEN(A1)+(LEN(A1)-C1)))

                Comment


                • #9
                  Re: Add Missing Leading Zeros To Text

                  Oh that is a better way of finding the first number, but (for the sake of my own ego) the concatenation is virtually identical...

                  Would it be possible to define "numbers" as an array {0,1...9} in the same way you can define constants in a workbook? This could be used in many different applications.

                  Comment


                  • #10
                    Re: Add Missing Leading Zeros To Text

                    Originally posted by great_big_bear
                    Would it be possible to define "numbers" as an array {0,1...9} in the same way you can define constants in a workbook? This could be used in many different applications.
                    Just tried it. Yes!

                    No, hang on - it's giving a different result.

                    Tried again - it works but you now have to enter the formula referring to the "numbers" array as an array formula.

                    Comment


                    • #11
                      Re: Add Missing Leading Zeros To Text

                      Thanks,

                      Works beautifully if the Site code is in A1 and I use:

                      =LEFT(A1,C1)&TEXT(RIGHT(A1,LEN(A1)-C1),REPT("0",6-LEN(A1)+(LEN(A1)-C1)))

                      in B1, and then:

                      =MIN( FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1

                      in C1.

                      Now I'm going to go away and try to unpick these so I can learn how they work.

                      Thanks,

                      Pete
                      Last edited by Rangerst; August 7th, 2008, 21:35. Reason: Auto Merged Doublepost

                      Comment


                      • #12
                        Re: Add Missing Leading Zeros To Text

                        Just realised that if any of your site codes are >6 characters in the first place you'll lose the number element.

                        You could adjust it to

                        =LEFT(A1,C1)&TEXT(RIGHT(A1,LEN(A1)-C1),REPT("0",MAX(6,C1+1)-LEN(A1)+(LEN(A1)-C1)))

                        Comment

                        Working...
                        X