Adding leading zero to numbers within alphanumeric string

[jp78kt]

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

[Norie]

Try this.

=IFERROR(TEXT(MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$15),1)),0),COUNT(1*MID(A2,ROW($1:$15),1))), "00"),"")

[jp78kt]

Norie,

Props on that - I'm hugely impressed. Is there an elegant way to add the rest of the string from the cell, all while keeping the leading zero where it needs to be (i.e. the output in the second column from my attachment)?

Cheers,
jp

[Norie]

I can't think of an elegant way to do it.

Once, kind of brute force, approach would be to replace what you find with the original formula with the result of the formula I posted.

That would involve this lovely formula.

=SUBSTITUTE(A8,IFERROR(MID(A8,MATCH(TRUE,ISNUMBER(1*MID(A8,ROW($1:$15),1)),0),COUNT(1*MID(A8,ROW($1:$15),1))),""), IFERROR(TEXT(MID(A8,MATCH(TRUE,ISNUMBER(1*MID(A8,ROW($1:$15),1)),0),COUNT(1*MID(A8,ROW($1:$15),1))), "00"),""))

[jp78kt]

Sometimes, brute force is what Excel needs.

Many thanks for a speedy solution!