convert numbers to text and maitain number of digits including preceeding zeros

New Contributor


I need to convert a column of numbers containing three digits (ie 001, 002, 003) into text, and maintain the zeros preceding the the non-zero digits. How can I do this?



I have two columns of numbers, both sets with zeros as the initial digit.  HOw can I combine the two columns?  I see how  to do it with both columns as text, but not as numbers.

I have  3,000 rows of data, so don't really want to convert each cell by hand with an apostrophe.


col A   col B     desired output

01      001         01001

01      003         01003

04      052         04052

15     167          15167



4 Replies


If column A and column B contain text values, use




in C2 and fill down. But if they contain numbers with custom number formats "00" and "000" respectively, use



This does not work-- puts all the zeros at the front and the nonzero digits at the end
col A col B output desired output
01 001 00011 01001


Could you attach a small sample workbook?

 This formula might help you :).


Keeping leading zeros and large numbers


If I may add, as Mr. Hans Vogelaar (@Hans Vogelaar) has already informed you, a more detailed description, if possible with a file or photos, would be of great advantage in order to get a quick and precise solution proposal. Here is a link with information on how you can get a quick and precise solution here in the forum.

Welcome to your Excel discussion space!


Thank you for your understanding and patience



I know I don't know anything (Socrates)