The reason I asked for what you have in the formula bar, is because twice
you've said that you loose the 0's at the beginning *and the end*.

<<"but without the dashes and the 0's at the beginning or end"

Your example however, shows that the zeroes at the *end*, stay at the *end*.

So, at least now, we can try something, since it's *only leading* zeroes
that we have to worry about.


This worked for me.
Add a column of text formulas to "pad" the existing numbers with leading
zeroes.
This will yield nine numbers, no dashes.
This makes this "helper" column into "Text" numbers, where you will be able
to retain the zeroes while in the TTC procedure.

Remove the text formulas, leaving just the data behind.

You can then split them with the break lines as we discussed earlier, but
with only 4 columns, since we've eliminated the dashes.
<<<<<<<<<<<<<<<<<<<<<<<
Formatted SSN in Column A

Enter this formula in B1, and *double* click the fill handle, so that the
formula in B1 is *automatically* copied down Column B, as far as there is
data in Column A.

=REPT("0",9-LEN(A1))&A1

NOW, to remove the formulas and leave the data, while you *still* have
Column B selected from the formula copy, right click in the selection and
choose "Copy".
Right click again, and choose "Paste Special".
Click on "Values", then <OK, then <Esc.

You now have a column of text numbers that should work perfectly in the TTC
procedure.

Don't forget which columns to make your first, second, and third sort keys.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"postitnote" wrote in message
...
I can't post an exact cell because they are actual SSNs which is why I
always
give obvious examples.

I can tell you that there are two columns which contain the SSNs, one is
titled SSN and the other is MRN (medical record number). The SSN column
is
fomatted as a SSN so you just type in the number and hit enter and the
dashes
show up automatically. The MRN column is set up as an SSN in Portuguese
(do
the same steps to format it as an SSN but when it asks you for location it
says "Portugal") so there are no dashes. When I change either of these
columns to text or number, the 0's at the beginning and end disappear.
With
over 11K names per list, there's no way I can go thru and add all the 0's
back in.

So the formula bar for the SSN column reads the same number but without
the
dashes and the 0's at the beginning or end, and the formula bar for the
MRN
column reads this same number as well.

SSN MRN
000-42-0200 000420200

fx: 420200

Thank you.