Add zero if sum of two characters within a string is less than ten

[FJCPA1]

Hi all.
I am a new registrant and have the following question.
I have a series of number strings such as:
1 – 2 – 18 – 25 – 45 – 5 – 8 – 17 – 99 – and so on.
Each number string is in a different cell (such as A1, A2, A3 and so on).
I want to add a number zero at each instance where a character in a string is less than ten so that all numbers within each string will have two characters, for example:
01 – 02 – 18 – 25 – 45 – 05 – 08 – 17 – 99
Each string is a different cell. The largest two character number within each string is 99. I am using Excel 2010 and Explorer 10. Any suggestions?
I will appreciate your responses.
Thank you.
Fred

[martindwilson]

with those numbers are in a1,a2 so on in b1 put =TEXT(A1,"00") fill down
alternatively you could format the cells using a custom format of 00

[FJCPA1]

Thank you martindwilson.

I did try using your suggestion (=TEXT(B1,"00") but this does not enter a zero before the characters 1 to 9 in the number strings. Any other suggestions?

Fred

[oeldere]

You added an ( to much on your formula.

try again with the offered formula, and you will see it work for you.

[galvinpaddy]

Good morning!

Would the following help?

If your data starts from A1, type

Please Login or Register  to view this content.

Into Cell B1, then copy down for as many rows have data in A.

Regards, Galvinpaddy

[galvinpaddy]

although Martindwilsons does work

[martindwilson]

ahh do you mean a1 contains
1 – 2 – 18 – 25 – 45 – 5 – 8 – 17 – 99
a2 contains say
1 – 3 – 18 – 30 – 45 – 41 – 8 – 9 – 88

[martindwilson]

ahh do you mean a1 contains
1 – 2 – 18 – 25 – 45 – 5 – 8 – 17 – 99
a2 contains say
1 – 3 – 18 – 30 – 45 – 41 – 8 – 9 – 88

[Melvinrobb]

in B1 insert this formula and drag across. Then just "Copy - Paste Values"

Please Login or Register  to view this content.

[FJCPA1]

Thank you all.

I tried your suggestions but none provides the solution I need.

I also tried: ="0"&IF(LEFT(BF41,2)<"10",LEFT(BF41,3)&MID(BF41,4,100),LEFT(BF41,2)&MID(BF41,3,100))
But this only inserts a zero at the beginning of each number string, not every time the sum of two characters is less than ten. For example, it converts:
21 – 2 – 18 – 25 – 45 – 5 – 8 – 17 – 99

Into:

021 – 2 – 18 – 25 – 45 – 5 – 8 – 17 – 99

Instead of:

21 – 02 – 18 – 25 – 45 – 05 – 08 – 17 – 99

Any suggestion will be greatly appreciated.

Fred

[martindwilson]

you will have to split and rejoin if you want to use a formula

[popipipo]

First: text to columns with delimiters other ' – '
Second: check =if(len(a1)=1,"0"&a1,a1) for each cel
Third: =A2&" – "&B2&" – "&C2&" – "&D2&" – "&E2&" – "&F2&" – "&G2&" – "&H2&" – "&I2