Menu
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.
801
If you're wanting to keep the leading zero on a single number value, insert a single apostrophe character (') before you type the number. That will tell Excel to treat the number as text and not monkey with it.
Method 1 - Add Apostrophe The first way to keep leading zeros in front of numbers is to put an apostrophe ' in front of the number. Notice how I input '0123 and not just 0123. Now, when I hit Enter, the number will keep its leading zero.
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
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
