Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Add Missing Leading Zeros To Text

  1. #1
    Join Date
    19th January 2008
    Posts
    3

    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 at 18:04.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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) 


  3. #3
    Join Date
    23rd April 2008
    Location
    Work in North Wales
    Posts
    262

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2008
    Posts
    3

    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 at 21:21.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

  6. #6
    Join Date
    11th December 2007
    Posts
    36

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  8. #8
    Join Date
    23rd April 2008
    Location
    Work in North Wales
    Posts
    262

    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)))

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    11th December 2007
    Posts
    36

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    23rd April 2008
    Location
    Work in North Wales
    Posts
    262

    Re: Add Missing Leading Zeros To Text

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Keep/Retain Leading Zeros When Importing Text File
    By demoniak in forum Excel General
    Replies: 2
    Last Post: June 5th, 2008, 09:31
  2. List Of Folder Names Missing Leading Zeros
    By ljoseph in forum Excel General
    Replies: 7
    Last Post: February 8th, 2008, 10:34
  3. Join Text With Number With Leading Zeros
    By dh273 in forum Excel General
    Replies: 4
    Last Post: July 25th, 2007, 18:36
  4. numbers to text and insert leading zeros
    By lorkmc in forum Excel General
    Replies: 5
    Last Post: September 16th, 2005, 05:34
  5. Leading zeros are missing after using Cleanit Macro
    By ag7777 in forum Excel General
    Replies: 7
    Last Post: November 10th, 2004, 20:52

Bookmarks

Posting Permissions

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