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

New Contributor

HI

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?

 

OR

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

 

Thanks

4 Replies

@akadd90 

If column A and column B contain text values, use

 

=A2&B2

 

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

 

=TEXT(A2,"00")&TEXT(B2,"000")

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

@akadd90 

Could you attach a small sample workbook?

 This formula might help you :).

=TEXT(A1,"00000")

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

 

NikolinoDE

I know I don't know anything (Socrates)

 

@akadd90