Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
postitnote
 
Posts: n/a
Default How can I sort SSNs in Terminal Digit Order using Excel?

I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the SSN
in three separate columns and then merging the columns together somehow?
  #2   Report Post  
Ken Wright
 
Posts: n/a
Default

Give us some examples of your data, and how you want it all to sort.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"postitnote" wrote in message
...
I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal

Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is

in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the

SSN
in three separate columns and then merging the columns together somehow?



  #3   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the SSN
in three separate columns and then merging the columns together somehow?


The simplest method would be to add a "helper column" that has the digit groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in column G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you specified.

Include this helper column in your sort table, and sort on that column. You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated using a macro.

If your numbers are NOT separated by dashes, but are rather a sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron
  #4   Report Post  
RagDyer
 
Posts: n/a
Default

TTC (Text To Columns) can separate out groups from your list of numbers
without bothering (changing - revising) your original column.
Sort on the extracted data, and then throw it away, without even touching
the original list, except to sort it, of course.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"postitnote" wrote in message
...
I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal

Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is

in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the

SSN
in three separate columns and then merging the columns together somehow?



  #5   Report Post  
postitnote
 
Posts: n/a
Default

An example would be the following:

Say I have five SSNs that I need to put in TDO. These SSNs a
123-45-6789
987-65-4321
567-89-1234
246-81-3579
192-83-7465

They would be in TDO like this:
987-65-4321
567-89-1234
192-83-7465
246-81-3579
123-45-6789

So in my original post I miswrote that it is the last four, followed by the
next two, followed by the first three. It's actually the last two,
second-to-last two, third-to-last two, then first three.



"Ken Wright" wrote:

Give us some examples of your data, and how you want it all to sort.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"postitnote" wrote in message
...
I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal

Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is

in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the

SSN
in three separate columns and then merging the columns together somehow?






  #6   Report Post  
postitnote
 
Posts: n/a
Default

This sounds like a very good idea though after looking at my spreadsheet I
must admit that I'm not sure what to do with this information. What is a
"helper column" and how do I get it? I did input the information you gave me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the column,
but it's not exactly what I need (see my reply to the post made by Ken in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the SSN
in three separate columns and then merging the columns together somehow?


The simplest method would be to add a "helper column" that has the digit groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in column G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you specified.

Include this helper column in your sort table, and sort on that column. You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated using a macro.

If your numbers are NOT separated by dashes, but are rather a sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron

  #7   Report Post  
RagDyer
 
Posts: n/a
Default

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits in the
middle and then separating out the dashes from the other numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column where there's
room to print out the 6 columns, and also this allows the original column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out the way you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"postitnote" wrote in message
...
This sounds like a very good idea though after looking at my spreadsheet I
must admit that I'm not sure what to do with this information. What is a
"helper column" and how do I get it? I did input the information you gave

me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the

column,
but it's not exactly what I need (see my reply to the post made by Ken in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to combine

five
years' worth of records onto one spreadsheet. Our office uses Terminal

Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it

is in
order with the last four digits, then the middle two, then the top

three.
Does anyone know if there is a way to do this without having to put the

SSN
in three separate columns and then merging the columns together

somehow?

The simplest method would be to add a "helper column" that has the digit

groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in column

G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you specified.

Include this helper column in your sort table, and sort on that column.

You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated using a

macro.

If your numbers are NOT separated by dashes, but are rather a sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron



  #8   Report Post  
Fred Smith
 
Posts: n/a
Default

As I understand it, you want to sort by the last (terminal) digit first.
Regardless of whether I understand this correctly, the way you get Excel to sort
properly is to create a column of cells which are ordered properly (many people
in this board call this a "helper column").

For example, insert a column after your SSN. Use a formula like:

=right(a1,9)&left(a1,8)

or whatever formula will allow Excel to sort the records the way you want. Then
sort on this column. You can hide the column if you don't want it to appear on
any reports.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"postitnote" wrote in message
...
I work in a medical records office and we are attempting to combine five
years' worth of records onto one spreadsheet. Our office uses Terminal Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it is in
order with the last four digits, then the middle two, then the top three.
Does anyone know if there is a way to do this without having to put the SSN
in three separate columns and then merging the columns together somehow?



  #9   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Tue, 7 Jun 2005 15:50:04 -0700, "postitnote"
wrote:

This sounds like a very good idea though after looking at my spreadsheet I
must admit that I'm not sure what to do with this information. What is a
"helper column" and how do I get it? I did input the information you gave me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the column,
but it's not exactly what I need (see my reply to the post made by Ken in
this thread).

Thank you for your help.


A "helper column" is just an extra column that you insert and will use, at
least temporarily, in your table.

Lets say you had the SSN's in Column A and other data related to these SSN's in
Columns B:F.

Click the A which selects the entire column. Then Select Insert from the main
menu and Column from the drop down sub-menu.

Everything moves over one and your SSN's are now in column B; Column A is now
your helper column.

Given your post to Ken, modify the formula I wrote to read:

=RIGHT(B2,2)&MID(B2,8,2)&MID(B2,5,2)&LEFT(B2,3)

Copy/drag this formula down as far as required.

Then select your entire table, including column A, and sort ascending (as
number) on Column A.

Finally, you can delete column A, or hide it.

This can be also done as a macro.



--ron
  #10   Report Post  
postitnote
 
Posts: n/a
Default

Ok, I tried all of the options given here and this one seems to be the best
solution. My only concern now is that the spreadsheets were given to us set
up as SSNs and Numbers and not as text so whenever I make it text to column
and one of the columns has a "04" in it, then only the four shows up. I have
over 11K names and SSNs on one list (and five lists) so how can I make those
0's appear without having to go thru each and every number to input the 0
manually?

Thank you!



"RagDyer" wrote:

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits in the
middle and then separating out the dashes from the other numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column where there's
room to print out the 6 columns, and also this allows the original column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out the way you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"postitnote" wrote in message
...
This sounds like a very good idea though after looking at my spreadsheet I
must admit that I'm not sure what to do with this information. What is a
"helper column" and how do I get it? I did input the information you gave

me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the

column,
but it's not exactly what I need (see my reply to the post made by Ken in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to combine

five
years' worth of records onto one spreadsheet. Our office uses Terminal

Digit
Order (or TDO) and it doesn't appear that Excel can sort this way as it

is in
order with the last four digits, then the middle two, then the top

three.
Does anyone know if there is a way to do this without having to put the

SSN
in three separate columns and then merging the columns together

somehow?

The simplest method would be to add a "helper column" that has the digit

groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in column

G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you specified.

Include this helper column in your sort table, and sort on that column.

You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated using a

macro.

If your numbers are NOT separated by dashes, but are rather a sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron






  #11   Report Post  
RagDyer
 
Posts: n/a
Default

Since your *original* data is *untouched*, I don't see what difference that
makes.

You're going to sort on 4 ... right?

You're going to throw it (04, 4)away when you're done anyway!

Perhaps you're missing the original concept of this procedure.
--
Regards,

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

"postitnote" wrote in message
...
Ok, I tried all of the options given here and this one seems to be the

best
solution. My only concern now is that the spreadsheets were given to us

set
up as SSNs and Numbers and not as text so whenever I make it text to

column
and one of the columns has a "04" in it, then only the four shows up. I

have
over 11K names and SSNs on one list (and five lists) so how can I make

those
0's appear without having to go thru each and every number to input the 0
manually?

Thank you!



"RagDyer" wrote:

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits in the
middle and then separating out the dashes from the other numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column where

there's
room to print out the 6 columns, and also this allows the original

column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out the way

you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"postitnote" wrote in message
...
This sounds like a very good idea though after looking at my

spreadsheet I
must admit that I'm not sure what to do with this information. What

is a
"helper column" and how do I get it? I did input the information you

gave
me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the

column,
but it's not exactly what I need (see my reply to the post made by Ken

in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to combine

five
years' worth of records onto one spreadsheet. Our office uses

Terminal
Digit
Order (or TDO) and it doesn't appear that Excel can sort this way

as it
is in
order with the last four digits, then the middle two, then the top

three.
Does anyone know if there is a way to do this without having to put

the
SSN
in three separate columns and then merging the columns together

somehow?

The simplest method would be to add a "helper column" that has the

digit
groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in

column
G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you specified.

Include this helper column in your sort table, and sort on that

column.
You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated using a

macro.

If your numbers are NOT separated by dashes, but are rather a

sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron






  #12   Report Post  
postitnote
 
Posts: n/a
Default

No, I understand what you were saying. I didn't try to actually sort once I
saw that the 0's had disappeared. My main concern was if the SSNs started
with 003 or 000 or something like that, it appeared that the whole number
would be completely off when sorted. But I just did a test run and
everything worked fine!

Thanks for your help!!



"RagDyer" wrote:

Since your *original* data is *untouched*, I don't see what difference that
makes.

You're going to sort on 4 ... right?

You're going to throw it (04, 4)away when you're done anyway!

Perhaps you're missing the original concept of this procedure.
--
Regards,

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

"postitnote" wrote in message
...
Ok, I tried all of the options given here and this one seems to be the

best
solution. My only concern now is that the spreadsheets were given to us

set
up as SSNs and Numbers and not as text so whenever I make it text to

column
and one of the columns has a "04" in it, then only the four shows up. I

have
over 11K names and SSNs on one list (and five lists) so how can I make

those
0's appear without having to go thru each and every number to input the 0
manually?

Thank you!



"RagDyer" wrote:

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits in the
middle and then separating out the dashes from the other numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column where

there's
room to print out the 6 columns, and also this allows the original

column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out the way

you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"postitnote" wrote in message
...
This sounds like a very good idea though after looking at my

spreadsheet I
must admit that I'm not sure what to do with this information. What

is a
"helper column" and how do I get it? I did input the information you

gave
me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the
column,
but it's not exactly what I need (see my reply to the post made by Ken

in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to combine
five
years' worth of records onto one spreadsheet. Our office uses

Terminal
Digit
Order (or TDO) and it doesn't appear that Excel can sort this way

as it
is in
order with the last four digits, then the middle two, then the top
three.
Does anyone know if there is a way to do this without having to put

the
SSN
in three separate columns and then merging the columns together
somehow?

The simplest method would be to add a "helper column" that has the

digit
groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in

column
G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you specified.

Include this helper column in your sort table, and sort on that

column.
You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated using a
macro.

If your numbers are NOT separated by dashes, but are rather a

sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron







  #13   Report Post  
postitnote
 
Posts: n/a
Default

Ok now that I went and wrote that everything worked fine I have to write
again and say it didn't work when I actually put all 45K names together.
Here is what is happening (again, with the 0's)...

I have the following SSNs:
111-11-4030
222-22-0400
333-33-7040
444-44-0500
555-55-0300
666-66-0040

In TDO, they should show up like this:
555-55-0300
222-22-0400
444-44-0500
111-11-4030
666-66-0040
333-33-7040

....but instead they are showing up like this:
111-11-4030
555-55-0300
666-66-0040
333-33-7040
222-22-0400
444-44-0500

....which is not TDO. I believe this is happening because of the 0's that
are disappearing at the end of the SSN, but I don't know for sure. Any
suggestions?



"RagDyer" wrote:

Since your *original* data is *untouched*, I don't see what difference that
makes.

You're going to sort on 4 ... right?

You're going to throw it (04, 4)away when you're done anyway!

Perhaps you're missing the original concept of this procedure.
--
Regards,

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

"postitnote" wrote in message
...
Ok, I tried all of the options given here and this one seems to be the

best
solution. My only concern now is that the spreadsheets were given to us

set
up as SSNs and Numbers and not as text so whenever I make it text to

column
and one of the columns has a "04" in it, then only the four shows up. I

have
over 11K names and SSNs on one list (and five lists) so how can I make

those
0's appear without having to go thru each and every number to input the 0
manually?

Thank you!



"RagDyer" wrote:

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits in the
middle and then separating out the dashes from the other numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column where

there's
room to print out the 6 columns, and also this allows the original

column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out the way

you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"postitnote" wrote in message
...
This sounds like a very good idea though after looking at my

spreadsheet I
must admit that I'm not sure what to do with this information. What

is a
"helper column" and how do I get it? I did input the information you

gave
me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed the
column,
but it's not exactly what I need (see my reply to the post made by Ken

in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to combine
five
years' worth of records onto one spreadsheet. Our office uses

Terminal
Digit
Order (or TDO) and it doesn't appear that Excel can sort this way

as it
is in
order with the last four digits, then the middle two, then the top
three.
Does anyone know if there is a way to do this without having to put

the
SSN
in three separate columns and then merging the columns together
somehow?

The simplest method would be to add a "helper column" that has the

digit
groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in

column
G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you specified.

Include this helper column in your sort table, and sort on that

column.
You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated using a
macro.

If your numbers are NOT separated by dashes, but are rather a

sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron







  #14   Report Post  
Ken Wright
 
Posts: n/a
Default

Use the helper column with this formula and then select all and sort on the
helper column.

=RIGHT(A2,2)&MID(A2,8,2)&MID(A2,5,2)&LEFT(A2,2)

Assumes your data starts in row 2

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip


  #15   Report Post  
Ragdyer
 
Posts: n/a
Default

I just sorted your example numbers, following the instructions that I posted
here, and they sorted *exactly* as you stated that they *should be*.

The procedure works fine!

Say the original numbers are in Column A, and the 6 parsed (separated)
columns are B to G.
B = first 3 numbers
C = dash
D = middle 2 numbers
E = dash
F = first 2 numbers of last set
G = last 2 numbers

After the 6 columns were parsed along side the original data, did you select
*all* columns.
Did you then set the *first* sort key as Column G (last 2 numbers),
*Second* key as Column F,
And *third* sort key as Column D?

It worked perfectly for me!!!
--
Regards,

RD

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


"postitnote" wrote in message
...
Ok now that I went and wrote that everything worked fine I have to write
again and say it didn't work when I actually put all 45K names together.
Here is what is happening (again, with the 0's)...

I have the following SSNs:
111-11-4030
222-22-0400
333-33-7040
444-44-0500
555-55-0300
666-66-0040

In TDO, they should show up like this:
555-55-0300
222-22-0400
444-44-0500
111-11-4030
666-66-0040
333-33-7040

...but instead they are showing up like this:
111-11-4030
555-55-0300
666-66-0040
333-33-7040
222-22-0400
444-44-0500

...which is not TDO. I believe this is happening because of the 0's that
are disappearing at the end of the SSN, but I don't know for sure. Any
suggestions?



"RagDyer" wrote:

Since your *original* data is *untouched*, I don't see what difference

that
makes.

You're going to sort on 4 ... right?

You're going to throw it (04, 4)away when you're done anyway!

Perhaps you're missing the original concept of this procedure.
--
Regards,

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

"postitnote" wrote in message
...
Ok, I tried all of the options given here and this one seems to be the

best
solution. My only concern now is that the spreadsheets were given to

us
set
up as SSNs and Numbers and not as text so whenever I make it text to

column
and one of the columns has a "04" in it, then only the four shows up.

I
have
over 11K names and SSNs on one list (and five lists) so how can I make

those
0's appear without having to go thru each and every number to input

the 0
manually?

Thank you!



"RagDyer" wrote:

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits in

the
middle and then separating out the dashes from the other numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column where

there's
room to print out the 6 columns, and also this allows the original

column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out the

way
you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"postitnote" wrote in message
...
This sounds like a very good idea though after looking at my

spreadsheet I
must admit that I'm not sure what to do with this information.

What
is a
"helper column" and how do I get it? I did input the information

you
gave
me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed

the
column,
but it's not exactly what I need (see my reply to the post made by

Ken
in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to

combine
five
years' worth of records onto one spreadsheet. Our office uses

Terminal
Digit
Order (or TDO) and it doesn't appear that Excel can sort this

way
as it
is in
order with the last four digits, then the middle two, then the

top
three.
Does anyone know if there is a way to do this without having to

put
the
SSN
in three separate columns and then merging the columns together
somehow?

The simplest method would be to add a "helper column" that has

the
digit
groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in

column
G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you

specified.

Include this helper column in your sort table, and sort on that

column.
You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated

using a
macro.

If your numbers are NOT separated by dashes, but are rather a

sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron










  #16   Report Post  
postitnote
 
Posts: n/a
Default

I just sorted that same data as well and it also worked for me. There must
be something within the spreadsheets that were given to us that is messing it
up because the last four digits I used in my example were taken directly from
SSNs that were out of order.

One thing is that the SSN is formatted as an SSN (format cells, number,
special, SSN). When I change this to a regular number, the 0's in the
beginning and end disappear.

I just tried your system with some SSNs that began with 0 and this is what
happens:

The SSN is 000-42-0400 for example. When I do Text to Column and it comes
up in the box to put in the dividers, it looks like this:

420400

....because the 0s in the front disappear. I have no choice but to put the
dividers like this:
420 | 40 |

....because that is where the lines go for SSNs that do not have 0s missing
from them. So when it is broken down, it goes into sort boxes that way. So
if I have two SSNs, 000-42-0400 and 123-45-6789, they look like this in the
extra columns:

420 40
123 45 67 89

....and get sorted as such.

I don't mean to be a pain, and your suggestion is great! Can you think of
what could be causing it to do this? It has to be something within the
spreadsheet, the way the cells are formatted, because I get the correct
output when I hand type sample SSNs into a new spreadsheet.

Thank you.




"Ragdyer" wrote:

I just sorted your example numbers, following the instructions that I posted
here, and they sorted *exactly* as you stated that they *should be*.

The procedure works fine!

Say the original numbers are in Column A, and the 6 parsed (separated)
columns are B to G.
B = first 3 numbers
C = dash
D = middle 2 numbers
E = dash
F = first 2 numbers of last set
G = last 2 numbers

After the 6 columns were parsed along side the original data, did you select
*all* columns.
Did you then set the *first* sort key as Column G (last 2 numbers),
*Second* key as Column F,
And *third* sort key as Column D?

It worked perfectly for me!!!
--
Regards,

RD

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


"postitnote" wrote in message
...
Ok now that I went and wrote that everything worked fine I have to write
again and say it didn't work when I actually put all 45K names together.
Here is what is happening (again, with the 0's)...

I have the following SSNs:
111-11-4030
222-22-0400
333-33-7040
444-44-0500
555-55-0300
666-66-0040

In TDO, they should show up like this:
555-55-0300
222-22-0400
444-44-0500
111-11-4030
666-66-0040
333-33-7040

...but instead they are showing up like this:
111-11-4030
555-55-0300
666-66-0040
333-33-7040
222-22-0400
444-44-0500

...which is not TDO. I believe this is happening because of the 0's that
are disappearing at the end of the SSN, but I don't know for sure. Any
suggestions?



"RagDyer" wrote:

Since your *original* data is *untouched*, I don't see what difference

that
makes.

You're going to sort on 4 ... right?

You're going to throw it (04, 4)away when you're done anyway!

Perhaps you're missing the original concept of this procedure.
--
Regards,

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

"postitnote" wrote in message
...
Ok, I tried all of the options given here and this one seems to be the
best
solution. My only concern now is that the spreadsheets were given to

us
set
up as SSNs and Numbers and not as text so whenever I make it text to
column
and one of the columns has a "04" in it, then only the four shows up.

I
have
over 11K names and SSNs on one list (and five lists) so how can I make
those
0's appear without having to go thru each and every number to input

the 0
manually?

Thank you!



"RagDyer" wrote:

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits in

the
middle and then separating out the dashes from the other numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column where
there's
room to print out the 6 columns, and also this allows the original
column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out the

way
you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"postitnote" wrote in message
...
This sounds like a very good idea though after looking at my
spreadsheet I
must admit that I'm not sure what to do with this information.

What
is a
"helper column" and how do I get it? I did input the information

you
gave
me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it changed

the
column,
but it's not exactly what I need (see my reply to the post made by

Ken
in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to

combine
five
years' worth of records onto one spreadsheet. Our office uses
Terminal
Digit
Order (or TDO) and it doesn't appear that Excel can sort this

way
as it
is in
order with the last four digits, then the middle two, then the

top
three.
Does anyone know if there is a way to do this without having to

put
the
SSN
in three separate columns and then merging the columns together
somehow?

The simplest method would be to add a "helper column" that has

the
digit
groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are located in
column
G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you

specified.

Include this helper column in your sort table, and sort on that
column.
You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated

using a
macro.

If your numbers are NOT separated by dashes, but are rather a
sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron









  #17   Report Post  
CLR
 
Posts: n/a
Default

To keep from losing the leading zeros, just be sure to format the columns
from GENERAL to TEXT during the Data TextToColumns procedure.........

Vaya con Dios,
Chuck, CABGx3



"postitnote" wrote in message
...
I just sorted that same data as well and it also worked for me. There

must
be something within the spreadsheets that were given to us that is messing

it
up because the last four digits I used in my example were taken directly

from
SSNs that were out of order.

One thing is that the SSN is formatted as an SSN (format cells, number,
special, SSN). When I change this to a regular number, the 0's in the
beginning and end disappear.

I just tried your system with some SSNs that began with 0 and this is what
happens:

The SSN is 000-42-0400 for example. When I do Text to Column and it comes
up in the box to put in the dividers, it looks like this:

420400

...because the 0s in the front disappear. I have no choice but to put the
dividers like this:
420 | 40 |

...because that is where the lines go for SSNs that do not have 0s missing
from them. So when it is broken down, it goes into sort boxes that way.

So
if I have two SSNs, 000-42-0400 and 123-45-6789, they look like this in

the
extra columns:

420 40
123 45 67 89

...and get sorted as such.

I don't mean to be a pain, and your suggestion is great! Can you think of
what could be causing it to do this? It has to be something within the
spreadsheet, the way the cells are formatted, because I get the correct
output when I hand type sample SSNs into a new spreadsheet.

Thank you.




"Ragdyer" wrote:

I just sorted your example numbers, following the instructions that I

posted
here, and they sorted *exactly* as you stated that they *should be*.

The procedure works fine!

Say the original numbers are in Column A, and the 6 parsed (separated)
columns are B to G.
B = first 3 numbers
C = dash
D = middle 2 numbers
E = dash
F = first 2 numbers of last set
G = last 2 numbers

After the 6 columns were parsed along side the original data, did you

select
*all* columns.
Did you then set the *first* sort key as Column G (last 2 numbers),
*Second* key as Column F,
And *third* sort key as Column D?

It worked perfectly for me!!!
--
Regards,

RD


--------------------------------------------------------------------------

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

!

--------------------------------------------------------------------------

-


"postitnote" wrote in message
...
Ok now that I went and wrote that everything worked fine I have to

write
again and say it didn't work when I actually put all 45K names

together.
Here is what is happening (again, with the 0's)...

I have the following SSNs:
111-11-4030
222-22-0400
333-33-7040
444-44-0500
555-55-0300
666-66-0040

In TDO, they should show up like this:
555-55-0300
222-22-0400
444-44-0500
111-11-4030
666-66-0040
333-33-7040

...but instead they are showing up like this:
111-11-4030
555-55-0300
666-66-0040
333-33-7040
222-22-0400
444-44-0500

...which is not TDO. I believe this is happening because of the 0's

that
are disappearing at the end of the SSN, but I don't know for sure.

Any
suggestions?



"RagDyer" wrote:

Since your *original* data is *untouched*, I don't see what

difference
that
makes.

You're going to sort on 4 ... right?

You're going to throw it (04, 4)away when you're done anyway!

Perhaps you're missing the original concept of this procedure.
--
Regards,

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

"postitnote" wrote in message
...
Ok, I tried all of the options given here and this one seems to be

the
best
solution. My only concern now is that the spreadsheets were given

to
us
set
up as SSNs and Numbers and not as text so whenever I make it text

to
column
and one of the columns has a "04" in it, then only the four shows

up.
I
have
over 11K names and SSNs on one list (and five lists) so how can I

make
those
0's appear without having to go thru each and every number to

input
the 0
manually?

Thank you!



"RagDyer" wrote:

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits

in
the
middle and then separating out the dashes from the other

numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column

where
there's
room to print out the 6 columns, and also this allows the

original
column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out

the
way
you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may

benefit!
==============================================

"postitnote" wrote in

message
...
This sounds like a very good idea though after looking at my
spreadsheet I
must admit that I'm not sure what to do with this information.

What
is a
"helper column" and how do I get it? I did input the

information
you
gave
me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it

changed
the
column,
but it's not exactly what I need (see my reply to the post

made by
Ken
in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to

combine
five
years' worth of records onto one spreadsheet. Our office

uses
Terminal
Digit
Order (or TDO) and it doesn't appear that Excel can sort

this
way
as it
is in
order with the last four digits, then the middle two, then

the
top
three.
Does anyone know if there is a way to do this without

having to
put
the
SSN
in three separate columns and then merging the columns

together
somehow?

The simplest method would be to add a "helper column" that

has
the
digit
groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are

located in
column
G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you

specified.

Include this helper column in your sort table, and sort on

that
column.
You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated

using a
macro.

If your numbers are NOT separated by dashes, but are rather

a
sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron











  #18   Report Post  
RagDyer
 
Posts: n/a
Default

Select a cell that has leading zeroes, and post back with exactly what the
cell displays, and exactly what the formula bar displays.
--
Regards,

RD

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

"postitnote" wrote in message
...
I just sorted that same data as well and it also worked for me. There

must
be something within the spreadsheets that were given to us that is messing

it
up because the last four digits I used in my example were taken directly

from
SSNs that were out of order.

One thing is that the SSN is formatted as an SSN (format cells, number,
special, SSN). When I change this to a regular number, the 0's in the
beginning and end disappear.

I just tried your system with some SSNs that began with 0 and this is what
happens:

The SSN is 000-42-0400 for example. When I do Text to Column and it comes
up in the box to put in the dividers, it looks like this:

420400

...because the 0s in the front disappear. I have no choice but to put the
dividers like this:
420 | 40 |

...because that is where the lines go for SSNs that do not have 0s missing
from them. So when it is broken down, it goes into sort boxes that way.

So
if I have two SSNs, 000-42-0400 and 123-45-6789, they look like this in

the
extra columns:

420 40
123 45 67 89

...and get sorted as such.

I don't mean to be a pain, and your suggestion is great! Can you think of
what could be causing it to do this? It has to be something within the
spreadsheet, the way the cells are formatted, because I get the correct
output when I hand type sample SSNs into a new spreadsheet.

Thank you.




"Ragdyer" wrote:

I just sorted your example numbers, following the instructions that I

posted
here, and they sorted *exactly* as you stated that they *should be*.

The procedure works fine!

Say the original numbers are in Column A, and the 6 parsed (separated)
columns are B to G.
B = first 3 numbers
C = dash
D = middle 2 numbers
E = dash
F = first 2 numbers of last set
G = last 2 numbers

After the 6 columns were parsed along side the original data, did you

select
*all* columns.
Did you then set the *first* sort key as Column G (last 2 numbers),
*Second* key as Column F,
And *third* sort key as Column D?

It worked perfectly for me!!!
--
Regards,

RD


--------------------------------------------------------------------------

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

!

--------------------------------------------------------------------------

-


"postitnote" wrote in message
...
Ok now that I went and wrote that everything worked fine I have to

write
again and say it didn't work when I actually put all 45K names

together.
Here is what is happening (again, with the 0's)...

I have the following SSNs:
111-11-4030
222-22-0400
333-33-7040
444-44-0500
555-55-0300
666-66-0040

In TDO, they should show up like this:
555-55-0300
222-22-0400
444-44-0500
111-11-4030
666-66-0040
333-33-7040

...but instead they are showing up like this:
111-11-4030
555-55-0300
666-66-0040
333-33-7040
222-22-0400
444-44-0500

...which is not TDO. I believe this is happening because of the 0's

that
are disappearing at the end of the SSN, but I don't know for sure.

Any
suggestions?



"RagDyer" wrote:

Since your *original* data is *untouched*, I don't see what

difference
that
makes.

You're going to sort on 4 ... right?

You're going to throw it (04, 4)away when you're done anyway!

Perhaps you're missing the original concept of this procedure.
--
Regards,

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

"postitnote" wrote in message
...
Ok, I tried all of the options given here and this one seems to be

the
best
solution. My only concern now is that the spreadsheets were given

to
us
set
up as SSNs and Numbers and not as text so whenever I make it text

to
column
and one of the columns has a "04" in it, then only the four shows

up.
I
have
over 11K names and SSNs on one list (and five lists) so how can I

make
those
0's appear without having to go thru each and every number to

input
the 0
manually?

Thank you!



"RagDyer" wrote:

Just repeating, TTC is *perfect* for what you're looking to do.

Select your column of numbers, then,
<Data <Text To Columns <Fixed Width <Next
Then create 5 break lines, separating your last set of 4 digits

in
the
middle and then separating out the dashes from the other

numbers.
Then click <Next

Now, change the address in the "Destination" box, to a column

where
there's
room to print out the 6 columns, and also this allows the

original
column of
numbers to remain intact, where they were.

Now click <Finish

You have your original data, with adjoining columns parsed out

the
way
you
separated them.
Now select them all, and sort in any order that you wish.

When you're done, throw away those extra columns.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may

benefit!
==============================================

"postitnote" wrote in

message
...
This sounds like a very good idea though after looking at my
spreadsheet I
must admit that I'm not sure what to do with this information.

What
is a
"helper column" and how do I get it? I did input the

information
you
gave
me
(=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)) and noticed how it

changed
the
column,
but it's not exactly what I need (see my reply to the post

made by
Ken
in
this thread).

Thank you for your help.



"Ron Rosenfeld" wrote:

On Tue, 7 Jun 2005 12:30:02 -0700, "postitnote"
wrote:

I work in a medical records office and we are attempting to

combine
five
years' worth of records onto one spreadsheet. Our office

uses
Terminal
Digit
Order (or TDO) and it doesn't appear that Excel can sort

this
way
as it
is in
order with the last four digits, then the middle two, then

the
top
three.
Does anyone know if there is a way to do this without

having to
put
the
SSN
in three separate columns and then merging the columns

together
somehow?

The simplest method would be to add a "helper column" that

has
the
digit
groups
in the order to be sorted -- then sort on that column.

If your digit groups are separated by dashes (and are

located in
column
G),
then:

=RIGHT(G1,4)&MID(G1,5,2)&LEFT(G1,3)

will return a text string of the digits in the order you

specified.

Include this helper column in your sort table, and sort on

that
column.
You
can then delete or hide the helper column.

If it is going to be a repetitive task, it can be automated

using a
macro.

If your numbers are NOT separated by dashes, but are rather

a
sequential
string, then use this formula:

=RIGHT(TEXT(G1,"000-00-0000"),4)&MID(TEXT(
G1,"000-00-0000"),5,2)&LEFT(TEXT(G1,"000-00-0000"),3)


--ron










  #19   Report Post  
postitnote
 
Posts: n/a
Default

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.
  #20   Report Post  
RagDyer
 
Posts: n/a
Default

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
In Excel, how do I sort a column by the LAST digit of the data? DougMash Excel Discussion (Misc queries) 2 May 12th 05 08:54 PM
How can I sort multiple months/years WITHOUT Alpha order taking o. LisaMU Excel Worksheet Functions 1 April 13th 05 04:46 PM
I have two identical pivot tables with different sort order of th. WilliamJFoster Excel Discussion (Misc queries) 1 April 7th 05 03:48 AM
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. [email protected] New Users to Excel 1 February 18th 05 01:59 AM
Need to sort dates before 1900 in proper order sandage_2000 Excel Discussion (Misc queries) 3 January 8th 05 04:31 AM


All times are GMT +1. The time now is 02:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"