I want to add leading "0s" to every number:
0000 & 1 = 00001But 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.
Best Answer
The following formula will format numbers so that they're padded with 0's up to 5 characters.
=TEXT(A1,"00000")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
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.