Leading zeros

[KyleSter]

Thank you taking the time to help!

I am importing and concatenating long text strings from multiple sources. These are actually numeric data but Excel's limit of 16 numeric characters prevents me from saving them as such. One of the strings that I 'tie' into a longer text value is a ten digit 'number'. This number frequently includes leading zeros but on the source formatted document (not Excel) these zeros are often 'stripped' before the data can be imported to Excel. My goal is to append any imported values less than ten digits in length to 'fill' with leading zeros.

Desired Value (in text format):
0123456789

Currently imported value (in text format)
123456789

Is there a function within Excel to do this neatly and conveniently?

Thank you

[Ace_XL]

=Try..

=IFERROR(REPT(0,10-LEN(A1))&A1,A1)

[amotto11]

this should work,

=IFERROR(REPT(0,10-LEN(A1))&A1,A1)

Edit: ace, You beat me to it.

[bolekblues]

Hi

Not sure if i understood correctly, but i would first of all changed the format of cells (where the values are imported) to text - this will prevent excel from autoamtically changing the format. If the value is without leading zeroes, you can simply use &, like ="0" & a1, where a1 is the number without leading zeros.

Or is your question about how to actually append those numbers one after another?

Bartek

[day92]

You can try..

=TEXT(A1,"0000000000")

[KyleSter]

Many Thanks! =IFERROR(REPT(0,10-LEN(A1))&A1,A1) does it in one shot!