text formatting

[mattmickle]

Value = $7,564,264.00

I need this to become a sixteen character text string:

0000000756426400

my formula =Text(Text(substitute(B13,".",""),"0000000000000000"

becomes 000000007564264

this works if, for example, the number is $5,469,062.72, but for any round number, it drops the decimal zeroes.

the last 2 digits should be implied decimals. How can i keep them?

 

[Ron Coderre]

With
A1: $7,564,264.00

This regular formula returns the string you're looking for:

B1:[/COLOR]=TEXT(--SUBSTITUTE(TEXT(A1,"0.00"),".",""),"0000000000000000")

This also seems to work:

B1: =TEXT(A1*100,"0000000000000000")

In those examples, B1 evaluates to: "0000000756426400"

Is that something you can work with?

 

[Joe4]

Try this:

=SUBSTITUTE(TEXT(B13,"00000000000000.00"),".","")

 

[mattmickle]

Joe4,
That worked! But, I figured out an easier way to accomplish it.

instead of substituting for the".", i just multiply the value times 100 in my Text formulas.
=Text(b13*100,"0000000000000000")

thanks for your help though!

 

[Joe4]

That worked! But, I figured out an easier way to accomplish it.

instead of substituting for the".", i just multiply the value times 100 in my Text formulas.
=Text(b13*100,"0000000000000000")

Yep, that works too.

That is one of the cool things about Excel. There is often multiple solutions to the same problem.