Sort by 2nd word in a cell?
 

If I create a data base with a name cell that includes both the first and
last name, can I sort by the first letter of the last name?

The first thing I'd do is to use two more columns and put the first and last
names into their own columns.

Data|Text to columns
seems like a good way to start.

Then sort by those helper columns.

Curious Lady wrote:

If I create a data base with a name cell that includes both the first and
last name, can I sort by the first letter of the last name?

--

Dave Peterson

If you have not created the DataBase yet, then by all means use separate
columns for the first and last names.......it's so much better in the long
run........if you already have such a list, then it's best to separate it
with the "TextToColumns" feature or the "MID" formula..............

Vaya con Dios,
Chuck, CABGx3

"Curious Lady" <Curious wrote in message
...
If I create a data base with a name cell that includes both the first and
last name, can I sort by the first letter of the last name?

Dave,

I already do the two columns but have difficulty transferring it into Word
(usually creating class rosters) without using mail merge. I am not sure
what you mean by Data|Text to columns. I will try using the help but any
quickie advice would be much appreciated. Thanks for your time.

"Dave Peterson" wrote:

The first thing I'd do is to use two more columns and put the first and last
names into their own columns.

Data|Text to columns
seems like a good way to start.

Then sort by those helper columns.

Curious Lady wrote:

If I create a data base with a name cell that includes both the first and
last name, can I sort by the first letter of the last name?

--

Dave Peterson

We already have the data base and I understand what you are telling me to do;
however, I am not familiar with the "TextToColumns" feature or the "MID"
formula. Where would I find this and can you suggest a good question to ask
Mr. Wizard Help? Thank you for your time.

"CLR" wrote:

If you have not created the DataBase yet, then by all means use separate
columns for the first and last names.......it's so much better in the long
run........if you already have such a list, then it's best to separate it
with the "TextToColumns" feature or the "MID" formula..............

Vaya con Dios,
Chuck, CABGx3

"Curious Lady" <Curious wrote in message
...
If I create a data base with a name cell that includes both the first and
last name, can I sort by the first letter of the last name?

You could use DataText to columns to break the cell into two columns then
sort on the last name.


Gord Dibben Excel MVP

On Thu, 23 Jun 2005 16:31:02 -0700, Curious Lady <Curious
wrote:

If I create a data base with a name cell that includes both the first and
last name, can I sort by the first letter of the last name?

Save your data and use a copy for this exercize........
Be sure the column to the right of your column is blank, or insert a new
one...........
Highlight the column with the names, then do Data TextToColumns
Delimited Next check the SPACE box finish
This splits the surname over to the blank column

or

If your data in column A, put this in B1 and copy down.........
=MID(A1,FIND(" ",A1,1)+1,99)
This will put the surname in column B, leaving column A intact

Vaya con Dios,
Chuck, CABGx3



"Curious Lady" wrote in message
...
We already have the data base and I understand what you are telling me to
do;
however, I am not familiar with the "TextToColumns" feature or the "MID"
formula. Where would I find this and can you suggest a good question to
ask
Mr. Wizard Help? Thank you for your time.

"CLR" wrote:

If you have not created the DataBase yet, then by all means use separate
columns for the first and last names.......it's so much better in the
long
run........if you already have such a list, then it's best to separate
it
with the "TextToColumns" feature or the "MID" formula..............

Vaya con Dios,
Chuck, CABGx3

"Curious Lady" <Curious wrote in message
...
If I create a data base with a name cell that includes both the first
and
last name, can I sort by the first letter of the last name?

Thanks a million. I am off to try it and if works you have no idea how much
joy you will brought to the world. :-)

"CLR" wrote:

Save your data and use a copy for this exercize........
Be sure the column to the right of your column is blank, or insert a new
one...........
Highlight the column with the names, then do Data TextToColumns
Delimited Next check the SPACE box finish
This splits the surname over to the blank column

or

If your data in column A, put this in B1 and copy down.........
=MID(A1,FIND(" ",A1,1)+1,99)
This will put the surname in column B, leaving column A intact

Vaya con Dios,
Chuck, CABGx3



"Curious Lady" wrote in message
...
We already have the data base and I understand what you are telling me to
do;
however, I am not familiar with the "TextToColumns" feature or the "MID"
formula. Where would I find this and can you suggest a good question to
ask
Mr. Wizard Help? Thank you for your time.

"CLR" wrote:

If you have not created the DataBase yet, then by all means use separate
columns for the first and last names.......it's so much better in the
long
run........if you already have such a list, then it's best to separate
it
with the "TextToColumns" feature or the "MID" formula..............

Vaya con Dios,
Chuck, CABGx3

"Curious Lady" <Curious wrote in message
...
If I create a data base with a name cell that includes both the first
and
last name, can I sort by the first letter of the last name?

It worked very well. Thank you. Can I then do the reverse action? If I
have 2 columns, first name, last name, can I merge them into a column that
contains the names together? Thank you again for your time.

"CLR" wrote:

Save your data and use a copy for this exercize........
Be sure the column to the right of your column is blank, or insert a new
one...........
Highlight the column with the names, then do Data TextToColumns
Delimited Next check the SPACE box finish
This splits the surname over to the blank column

or

If your data in column A, put this in B1 and copy down.........
=MID(A1,FIND(" ",A1,1)+1,99)
This will put the surname in column B, leaving column A intact

Vaya con Dios,
Chuck, CABGx3



"Curious Lady" wrote in message
...
We already have the data base and I understand what you are telling me to
do;
however, I am not familiar with the "TextToColumns" feature or the "MID"
formula. Where would I find this and can you suggest a good question to
ask
Mr. Wizard Help? Thank you for your time.

"CLR" wrote:

If you have not created the DataBase yet, then by all means use separate
columns for the first and last names.......it's so much better in the
long
run........if you already have such a list, then it's best to separate
it
with the "TextToColumns" feature or the "MID" formula..............

Vaya con Dios,
Chuck, CABGx3

"Curious Lady" <Curious wrote in message
...
If I create a data base with a name cell that includes both the first
and
last name, can I sort by the first letter of the last name?

=A1&" "&B1

or =B1&", "&A1

just a note. It's usually lots easier to concatenate stuff (put it together)
than to separate fields.

Dr. James Throckmorton Smythe-Davis, III

would be difficult to separate.

Curious Lady wrote:

It worked very well. Thank you. Can I then do the reverse action? If I
have 2 columns, first name, last name, can I merge them into a column that
contains the names together? Thank you again for your time.

"CLR" wrote:

Save your data and use a copy for this exercize........
Be sure the column to the right of your column is blank, or insert a new
one...........
Highlight the column with the names, then do Data TextToColumns
Delimited Next check the SPACE box finish
This splits the surname over to the blank column

or

If your data in column A, put this in B1 and copy down.........
=MID(A1,FIND(" ",A1,1)+1,99)
This will put the surname in column B, leaving column A intact

Vaya con Dios,
Chuck, CABGx3



"Curious Lady" wrote in message
...
We already have the data base and I understand what you are telling me to
do;
however, I am not familiar with the "TextToColumns" feature or the "MID"
formula. Where would I find this and can you suggest a good question to
ask
Mr. Wizard Help? Thank you for your time.

"CLR" wrote:

If you have not created the DataBase yet, then by all means use separate
columns for the first and last names.......it's so much better in the
long
run........if you already have such a list, then it's best to separate
it
with the "TextToColumns" feature or the "MID" formula..............

Vaya con Dios,
Chuck, CABGx3

"Curious Lady" <Curious wrote in message
...
If I create a data base with a name cell that includes both the first
and
last name, can I sort by the first letter of the last name?







--

Dave Peterson

You're very welcome...........yes, it can be reversed........ the process is
called CONCATENATION...........
Assuming Firstname in Column A and Lastname in B, put one of these in
C1.......
=A1&" "&B1 will result in Firstname Lastname
=B1&", "&A1 will result in Lastname, Firstname

Vaya con Dios,
Chuck, CABGx3


"Curious Lady" wrote in message
...
It worked very well. Thank you. Can I then do the reverse action? If I
have 2 columns, first name, last name, can I merge them into a column that
contains the names together? Thank you again for your time.

"CLR" wrote:

Save your data and use a copy for this exercize........
Be sure the column to the right of your column is blank, or insert a new
one...........
Highlight the column with the names, then do Data TextToColumns
Delimited Next check the SPACE box finish
This splits the surname over to the blank column

or

If your data in column A, put this in B1 and copy down.........
=MID(A1,FIND(" ",A1,1)+1,99)
This will put the surname in column B, leaving column A intact

Vaya con Dios,
Chuck, CABGx3



"Curious Lady" wrote in message
...
We already have the data base and I understand what you are telling me
to
do;
however, I am not familiar with the "TextToColumns" feature or the
"MID"
formula. Where would I find this and can you suggest a good question
to
ask
Mr. Wizard Help? Thank you for your time.

"CLR" wrote:

If you have not created the DataBase yet, then by all means use
separate
columns for the first and last names.......it's so much better in
the
long
run........if you already have such a list, then it's best to
separate
it
with the "TextToColumns" feature or the "MID" formula..............

Vaya con Dios,
Chuck, CABGx3

"Curious Lady" <Curious wrote in
message
...
If I create a data base with a name cell that includes both the
first
and
last name, can I sort by the first letter of the last name?

=A9&" "&B9&" "&D9&" "&E9
And again it worked. Wonder what you do to know this. The above will
separate the Dr.'s name. It took me a while to get the formula in
correctly, not sure why, but then you can just use the fill. Interesting.
Thanks again.

"Dave Peterson" wrote:

=A1&" "&B1

or =B1&", "&A1

just a note. It's usually lots easier to concatenate stuff (put it together)
than to separate fields.

Dr. James Throckmorton Smythe-Davis, III

would be difficult to separate.

Curious Lady wrote:

It worked very well. Thank you. Can I then do the reverse action? If I
have 2 columns, first name, last name, can I merge them into a column that
contains the names together? Thank you again for your time.

"CLR" wrote:

Save your data and use a copy for this exercize........
Be sure the column to the right of your column is blank, or insert a new
one...........
Highlight the column with the names, then do Data TextToColumns
Delimited Next check the SPACE box finish
This splits the surname over to the blank column

or

If your data in column A, put this in B1 and copy down.........
=MID(A1,FIND(" ",A1,1)+1,99)
This will put the surname in column B, leaving column A intact

Vaya con Dios,
Chuck, CABGx3



"Curious Lady" wrote in message
...
We already have the data base and I understand what you are telling me to
do;
however, I am not familiar with the "TextToColumns" feature or the "MID"
formula. Where would I find this and can you suggest a good question to
ask
Mr. Wizard Help? Thank you for your time.

"CLR" wrote:

If you have not created the DataBase yet, then by all means use separate
columns for the first and last names.......it's so much better in the
long
run........if you already have such a list, then it's best to separate
it
with the "TextToColumns" feature or the "MID" formula..............

Vaya con Dios,
Chuck, CABGx3

"Curious Lady" <Curious wrote in message
...
If I create a data base with a name cell that includes both the first
and
last name, can I sort by the first letter of the last name?







--

Dave Peterson