Concatenate function - keeping "displayed" extra digits
 

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.

One way:

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


In article ,
"EricKei" wrote:

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.

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?
--
Regards,
Ron

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

Does that help?


Yes! It worked like a charm :) Thanks again!