Numbe to Text

[tt388]

I have a column with numbers on a worksheet. I would like to convert the number to text. All converted text should have 15 characters. If the original number has less than 15 characters, fill space from the left of the text.

Eg.

20000.00 will become " 20000.00". ( no quotation marks in the cell)

Thanks in advance for any help.

[NBVC]

you mean?

Please Login or Register  to view this content.

12 spaces after first "", then .00"

[tt388]

you mean?

Please Login or Register  to view this content.

12 spaces after first "", then .00"

Not really. The total number of character of text should be 15 for all cells. The last 3 character is always ".00".

For example,

if the number is 5000, the return value should be ("8 space"5000.00).
if the number is 2000000.00, the return value should be ("5 space"2000000.00)

[MrShorty]

Have you tried the ? format code? =text(a1,"????????????0.00")
And note that I've set it up to return 16 characters (15 digits), which I think is what you really intended. The lowly decimal point is still a character. If I've misinterpreted, that requirement, adjust accordingly. If it still doesn't work, explain what it does and doesn't do correctly.

[tt388]

Mr Shorty,

The formula does not really resolve the situation. Certainly, the decimal point considers one character. See the example in the above correspondnace of what I am looking for.

Thanks...

[NBVC]

Have you tried the formula.... for me it works.

=TEXT(A1,"????????????.00") will give you 15 characters, including spaces.

[tt388]

NBVC and MrShorty,

The formula works now if I use "?" instead of "Space".

Great Help.. Thanks a lot...