concatenation

[Jacque]

I want to concatenate three columns of numbers to
reestablish SS# from a screwed up database I am trying to
fix.

The orginator of the DB use the SS# format which is
causeing all kinds of problems when trying to rebuild the
data. So I had the bright idea to send the data to
excel, seperate it using Text to columns. That worked
fine. I then used custom format so leading zeros would
show up in the cells where approparte.

Here is the problem. When trying to us concatenate to
reconstruct the numbers all the leading zeros
disapear.... Help.

I usally think of the hard way to do something first so
if anyone has a better idea I would love to hear it.
Thanks
Jacque

 

[Tim Otero]

You're actually pretty close. You need to turn the first column into text.
One way:
In a helper cell enter this formula and copy it down the column, as needed
(provided your range begins in A1):

=TEXT(A1,"000")

Next copy the column and paste special|values. Use this column for your
concatenation formula:

=A1&B1&C1 or
=A1&"-"&B1&"-"&C1

whichever you prefer.

tim