» Combining Numbers that Have Leading Zeros
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
Column A (List1) is formatted to display a leading zero before each number. That is, "1" is displayed as "01", "2" as "02", and so on.
The following formula was created to concatenate the numbers from List1 into one string:
=A2&A3&A4&A5
However, an incorrect result of "1234" is returned.
How can we modify the formula to honor the leading zeros?
Solution:
Use the TEXT function as shown in the following formula:
=TEXT(A2,"00")&TEXT(A3,"00")&TEXT(A4,"00")&TEXT(A5,"00")
Book Store:
Recommended Books:
- The Essential 55: An Award-Winning Educator's Rules for Discovering the Successful Student in Every Child
- Preparing the Marketing Plan (Ama Marketing Toolbox Series New Edition)
- Fish! A Remarkable Way to Boost Morale and Improve Results
- East of Eden (Oprah's Book Club)
- Keys to Reading an Annual Report (Barron's Business Keys)
- Learn MS Excel 2002 VBA/XML Programming
Thank you very much for letting me know about TEXT function. It was very helpful in solving one of main problem in displaying leading zeros using ASP in excel. Thanks again.
Cheers,
Ravee