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 <[email protected]>,
"EricKei" <[email protected]> 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!