Add leading zeros

[j3andc]

I am creating a file that will be used to upload data to a website. the information is very specific in format. Specifically the text string has to be a specific length. This is a problem when I have an amount, because the amount is variable and therefore the leading zeros change with each record. Is there a way to tell excel to add enough zeros to the front edge of the text string to make the new text string 10 characters?

Thanks for any help.

Jim

 

[Joe4]

If you wanted to zero pad cell A1 so that it is exactly 10 spaces:

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

 

[Beyond_avarice]

=TEXT(insert reference cell,"0000000000")

 

[Joe4]

=TEXT(insert reference cell,"0000000000")

Just be careful with that. That assumes that you just have numbers (entered as numbers), and no decimals.
So it wouldn't work for things like text entries (I often see numbers entered as text) or decimals.

 

[Rick Rothstein]

=TEXT(insert reference cell,"0000000000")

Joe4's formula limits the output to exactly 10 digits... if an 11-digit number is put into the referenced cell, his formula output alerts the user that there are too many digits to form the required 10-digit number. Your formula, on the other hand, would output the full 11 digits without warning (and that value could not be used in the way it is needed).

 

[Beyond_avarice]

Just be careful with that. That assumes that you just have numbers (entered as numbers), and no decimals.
So it wouldn't work for things like text entries (I often see numbers entered as text) or decimals.

Good point. Thank you.