Concatenate Question

[Jim Garman]

I'm trying to concatenate the following information:

EDF - 000053

So that it will appear: EDF-000053. I have set up
the "000053" cell so that it adds the leading zeros, but
when I concatenate the three cells, I lose the leading
zeros. I end up with "EDF-53". I need those leading
zeros so that the data sorts properly.

How can I ensure that the leading zeros will remain?
Thanks, \JG

 

[Andy B]

Hi

It sounds like you've told Excel to display the leading zeroes - but that
does not alter what is in the cell. Try:
=A2&"-"&REPT("0",6-LEN(B2))&B2
to get a 6-digit 'number'

 

[Peo Sjoblom]

Try

=A1&" - "&TEXT(B1,"000000")

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Jim Garman]

Beautiful. Thank you. \JG

-----Original Message-----
Try

=A1&" - "&TEXT(B1,"000000")

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom




.

 

[Guest]

-----Original Message-----
I'm trying to concatenate the following information:

EDF - 000053

So that it will appear: EDF-000053. I have set up
the "000053" cell so that it adds the leading zeros, but
when I concatenate the three cells, I lose the leading
zeros. I end up with "EDF-53". I need those leading
zeros so that the data sorts properly.

How can I ensure that the leading zeros will remain?
Thanks, \JG
.

I'm assuming that the data in the column containing the
cell "000053" has different number values in it, for
example "000054" etc..

Using the "text to columns..." from the data menu just
convert the "000053" column data to text before you
concatenate. Done deal.