Concatenation in Excel

[Muthalaly]

Hi,
I am trying to concatenate two fields in excel.In this one field is a cutom
number field .When I am doing the concatenation I am losing the zeros before
the number.
Example 00123 + abc I am getting a result of 123abc .But I am looking
forward to get 00123abc. Is it possible in excel?Please help.

 

[FSt1]

hi
i suspect that the 00123 is formated with leading zeros meaning that the
zeros are not there to concatinate. i think you may have to reform as text
and add the leading zeros.

regards
FSt1

 

[MartinW]

Hi Muthalaly,

This is a bit long winded but you may be able to work it in to your formula.
=IF(LEN(A1)=1,"0000"&A1,IF(LEN(A1)=2,"000"&A1,IF(LEN(A1)=3,"00"&A1,IF(LEN(A1)=4,"0"&A1,A1))))

With A1 formatted as custom 00000, this will add the formatted
zeroes to your concatenation. Just add the abc where appropriate.

HTH
Martin

 

[Pete_UK]

Try this:

=TEXT(A1,"00000") & B1

with your number in A1 and the text in B1.

Hope this helps.

Pete

 

[MartinW]

Derrr!! <bg>

No excuses
Martin

Try this:

=TEXT(A1,"00000") & B1

with your number in A1 and the text in B1.

Hope this helps.

Pete

 

[Muthalaly]

Thank you so much for the help!!!

Try this:

=TEXT(A1,"00000") & B1

with your number in A1 and the text in B1.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete