Concatenate function - keeping "displayed" extra digits

[EricKei]

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.

 

[JE McGimpsey]

One way:

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

 

[Ron Coderre]

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?

 

[EricKei]

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

Does that help?

Yes! It worked like a charm Thanks again!