Concatenate function - keeping "displayed" extra digits

G

Guest

I am using the following setup to force leading zeroes in my cells (thanks to
another poster here) :

Format Cells > Custom > 00000

This causes Excel to display any numbers with fewer than 5 digits in a
manner such as 00067 (if the original number was 67, for example).

My question is this: When using the CONCATENATE(A1&B1) function to join the
cells in question, any cells whose source data contained fewer than 5 digits
to begin with get Concatenated without those leading zeroes. I tried
formatting them as ZIP Codes (automatically adds the leading zeroes), but
Concatenate still strips out the "extra" zeroes. How would I change this such
that it Concatenates full 5-digit entry, rather than the 2-4 digits
origianally inserted?

ie, it turns 00167 00465 into 167465

Thanks in advance for yor help.
 
G

Guest

Keep in mind that formatting only changes the way a value is displayed, not
the actual value. Consequently, I think you'd need something like this:

=CONCATENATE(TEXT(A1,"00000")&TEXT(B1,"00000"))

Or, this should also work:

=TEXT(A1,"00000")&TEXT(B1,"00000")


Does that help?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top