Text format 1.34.36.12 to look like 01343612

A

AFSSkier

Does any on know how to format 1.34.36.12 to look like 01343612
I tried: Text([source],"00000000") in excel with no resolute.
 
A

Alan

Not sure about a format, but the formula
=0&SUBSTITUTE(A1,".","")
will do it, copy and Paste Special Values to lose the formula,
Regards,
Alan.
 
A

AFSSkier

Your formula =0&SUBSTITUTE(A1,".","") added an extra space, 0 1343612
However, =TEXT(SUBSTITUTE(A1,".",""),"00000000") does work, 01343612.
--
Thanks for the tip, Kevin


Alan said:
Not sure about a format, but the formula
=0&SUBSTITUTE(A1,".","")
will do it, copy and Paste Special Values to lose the formula,
Regards,
Alan.
AFSSkier said:
Does any on know how to format 1.34.36.12 to look like 01343612
I tried: Text([source],"00000000") in excel with no resolute.
Click to expand...
Click to expand...
 
D

Dave Peterson

=TEXT(SUBSTITUTE(A1,".",""),REPT("0",8))


Does any on know how to format 1.34.36.12 to look like 01343612
I tried: Text([source],"00000000") in excel with no resolute.
Click to expand...
 
A

Alan

Glad you have a solution but that formula doesn't add an extra space for me
if I enter 1.34.36.12 into A1 manually. Are you importing the data from
somewhere?, if so it may have a leading zero. An alternative formula would
be
=0&TRIM(SUBSTITUTE(A1,".",""))
Regards,
Alan.
AFSSkier said:
Your formula =0&SUBSTITUTE(A1,".","") added an extra space, 0 1343612
However, =TEXT(SUBSTITUTE(A1,".",""),"00000000") does work, 01343612.
--
Thanks for the tip, Kevin


Alan said:
Not sure about a format, but the formula
=0&SUBSTITUTE(A1,".","")
will do it, copy and Paste Special Values to lose the formula,
Regards,
Alan.
AFSSkier said:
Does any on know how to format 1.34.36.12 to look like 01343612
I tried: Text([source],"00000000") in excel with no resolute.
Click to expand...
Click to expand...
Click to expand...
 
Top