Concatenate Question

  • Thread starter Thread starter Jim Garman
  • Start date Start date
J

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
 
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'
 
Try

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

--


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

Regards,

Peo Sjoblom
 
Beautiful. Thank you. \JG

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

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

--


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

Regards,

Peo Sjoblom




.
Click to expand...
 
-----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
.
Click to expand...
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.
 
Back
Top