[solved] convert variable length decimal to fixed string w/left and right padding

[jayz2949]

Hello,
I need to convert a decimal to a fixed length 15 character string. The decimal varies in length, both to the left and right of the decimal. When converted to a sting, the right 8 characters represent the characters to the right of the decimal and the string cannot exceed 15 characters. Variable length padding is required on each side of the decimal.

The first thought is create an UDF, that might be wrong. I've tried nesting various if, trim, find, contatenate, etc...and am honestly out of ideas.

e.g. 1.23 converted to string is 000000123000000
e.g. 1.234 converted to string is 000000123400000
e.g. 111.234 converted to string is 000011123400000

Please help!

[FDibbins]

try this. you may have to add/subtract zero's to get it as you want it...

=SUBSTITUTE(TEXT(A1,"00000000.0000000"),".","")

[jayz2949]

That seems to pass various tests...many thanks for a quick response!!

[FDibbins]

you're welcome, and thanks for the rep