how to keep leading and trailing zeros

[michaelsmith559]

I have split a long decimal number into three columns (3 digits per column). I did a custom number format and got the numbers to keep the zeros. I further split the numbers into two cells (one column has one digit, the other column has two digits). The problem is now when I try and concatenate them using this formula:
"=concatenate(a1,".",b1)" , if there is a leading zero in the column with two digits it will not include it. So if I try this formula and the number happens to be 7 in A1, 08 in B1 and try the formula it returns 7.8 and not 7.08. How can I fix this?

 

[S.H.A.D.O.]

Give this a try...

=CONCATENATE(A1,".",TEXT(B1,"00"))

I hope this helps!

 

[BarryL]

whats your custom format? try use the custom format of just an @ symbol

 

[Rick Rothstein]

I have split a long decimal number into three columns (3 digits per column). I did a custom number format and got the numbers to keep the zeros. I further split the numbers into two cells (one column has one digit, the other column has two digits). The problem is now when I try and concatenate them using this formula:
"=concatenate(a1,".",b1)" , if there is a leading zero in the column with two digits it will not include it. So if I try this formula and the number happens to be 7 in A1, 08 in B1 and try the formula it returns 7.8 and not 7.08. How can I fix this?

Did you split the 3-digit number into 1-digit/2-digits solely for the purpose of being able to create the number with the decimal point in it? If so, you could have done that directly when you formatted the number to keep the zeroes... instead of using 000 you could use 0.00 to produce the 3-digit number with the included decimal point.

 

[michaelsmith559]

Worked great. Thanks.

 

[BarryL]

are you referring to S.H.A.D.O's method? for clarity for people with the same issue.