formula to insert zeros in front of numbers

[kelly mort]

Hello all,
I have in cells G9 the formula

 G9 = "0208888"&H9

So in H9 is a lookup formula. Then this is what I wanna do: When the number in H9 is one digit number , then place two zeros in front of it in the formula above. If two digits then place only one zeros in front of it.
Thanks in advance.
Kelly

 

[tommylee_b]

The following assumes there will only be 1 or 2 digits in cell H9. If there are more, it treats it like there is 1 digit and adds one zero.

G9 ="0208888"&IF(LEN(H9)=2,"0"&H9,"00"&H9)

 

[Eric W]

Also:

="020888"&TEXT(H9,"000")

 

[Rick Rothstein]

If the 020888 is fixed and never changing, then this will work...

=TEXT(H9,"\02\0888000")

 

[kelly mort]

G9 ="0208888"&IF(LEN(H9)=2,"0"&H9,"00"&H9)

Okay thanks very much. I have added a third argument to take care of my need.

G9 ="0208888"&IF(LEN(H9)=3,H9,IF(LEN(H9)=2,"0"&H9,"00"&H9))

 

[kelly mort]

Also:

="020888"&TEXT(H9,"000")

Yes!!!!! This is simple and did the job. Thanks again.
Kelly

 

[kelly mort]

If the 020888 is fixed and never changing, then this will work...

=TEXT(H9,"\02\0888000")

Oh okay this one too is simple and cool yet it shows only three of the 8s instead of four. Regards.
Kelly

 

[Rick Rothstein]

Oh okay this one too is simple and cool yet it shows only three of the 8s instead of four. Regards.
Kelly

I took the number of 8's from Eric's post (it was his formula that gave me the idea for mine). Anyway, the fix is quite simple... just put another 8 into the text...

=TEXT(H9,"\02\08888000")

 

[kelly mort]

I took the number of 8's from Eric's post (it was his formula that gave me the idea for mine). Anyway, the fix is quite simple... just put another 8 into the text...

=TEXT(H9,"\02\08888000")

Sure the fix was simple. I also got confused because of Eric's post. But was able to resolve it later. My network was down so I was not able to post it back. Thanks again.
Kelly