How to concatenate leading zeros in excel

Question

I want to add leading "0s" to every number:

0000 & 1 = 00001

But I need to count the length because the total number of characters shouldn't exceed 5, so if the number is 30 then then excel should show it as "00030".

If it's 100 then "00100".

I will then concatenate the following number onto the result: 1027, so, for 100, the end result would be 102700100.

Unborn

Answer 1

The following formula will format numbers so that they're padded with 0's up to 5 characters.

=TEXT(A1,"00000")

Amplifier

Excellent. Found this via google and it's simple and concise. +1 – Papilloma

Answer 2

You can use the formatting options in the TEXT() function. The syntax is TEXT(value, format_text), so in your example you'd use a formula like:

=TEXT(A1,"00000")

To join the two numeric strings together

=CONCATENATE("1027", TEXT(A1,"00000"))

See: http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx

Kela

This method definitely worked the best for me. I was combining zip-codes with trailing text – Goodness

Answer 3

The right function will do what you want, if you put a bunch of zeroes before your number. If you want 5 digits total, you'd do something like this:

=right("00000" & 45, 5)

This would output 00045.

If you are putting another number before it, you can just keep concatenating, like this:

=1027 & right("00000" & 45, 5)

You can, of course, replace any of those values with cell references to make it all more dynamic.

Unwind

Answer 4

Here is my Solution

For the first result, use "=CONCATENATE(IF(LEN(A2)>3,RIGHT(TEXT(A2,"000"),3),TEXT(A2,"000")),"",B2,"",C2)"

• a) 3 with 4 here - ",3)" as ",4)"
• b) 3 with 4 here - "RIGHT(TEXT(A2,"000"),3)" as "RIGHT(TEXT(A2,"000"),4)"
• c) 3 with 4 here - "LEN(A2)>3" replace as "LEN(A2)>4"
• d) add more Zeros in the Text operation "RIGHT(TEXT(A7,"000")" replace as "RIGHT(TEXT(A7,"0000")" -

Cobnut