Left Padding data

[gmoorthy]

In excel i have a bunch of data as follows


3456.89
788888.90

The total length of the field is 13 and the (.) gets replaced by (,) i want to do it as follows

0000003456,89
0000788888,90

Any easy way to write a formula

 

[SkipVought]

Use the TEXT function to pad with ZEROS and return TEXT only, as...
[tt]
=TEXT(A2,"0000000000.00")
[/tt]

Then COPY the column, right-click Edit > Paste Special -- VALUES and do a FIND & REPLACE . to ,

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[macropod]

Or you could just use:
=SUBSTITUTE(TEXT(A1,"0000000000.00"),".",",")

Cheers
Paul Edstein
[MS MVP - Word]

 

[byundt]

You can do it without need for SUBSTITUTE or Find & Replace using:
=TEXT(100*A1,"0000000000\,00")

 

[byundt]

If you want to change the value being displayed but not the underlying data, consider formatting the cell using Custom format:
0000000000\,00
%

The above Custom format has an ALT + Enter after \,00 and before %.

You will also need to turn Wrap Text on and Align the cell contents to the top (rather than the default bottom). Finally, you will need to change the row height so the % on the second line is hidden.

 

[xlbo]

Cat .skin. Many ways!
=rept("0",13-len(a1))&substitute(a1,".",".")
Untested

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[strongm]

I'd suggest that byundt's idea of using formatting is probably the way to go, as this simply looks like a regional issue (using a , as the decimal separator rather than .). So these are still numbers, and it may be are needed in calculations.