Results 1 to 5 of 5

Adding leading zero to numbers within alphanumeric string

Threaded View

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    5

    Adding leading zero to numbers within alphanumeric string

    Hi all,

    I need to add a leading zero to any number, 1-9, contained within a string. If the number is greater than 9, the leading zero should not be added.

    I've attached a sample xls here; the first column is the raw data and the second column is the output I expect. I've been able to extract the number included with a CSE of
    =IFERROR(MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$15),1)),0),COUNT(1*MID(A2,ROW($1:$15),1))),"")
    I'm stuck though. I can't figure out how to easily (or not so easily) add the leading zero where needed then re-concatenate everything back into a neat little package. I am saddened to report that I have spent two solid hours on various hacks, none of which produce the correct output.

    Any ideas on how to assist would be greatly appreciated.

    Thanks,
    jp
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1