Sorting

  • Thread starter Thread starter dleo
  • Start date Start date
D

dleo

I have a long list of people extracted from a database. Some of the names are
in the format of "John Smith" or "Jane Smith" on different lines, but some of
them are "John and Jane Smith."

How can I sort my list so that I can group all of the cells that contain the
word "and" in it?
 
Use a helper column.

Insert a new column next to your data. For this example, I'll assume your
data is in column A and the helper column is column B. Type this formula
into the cell in the helper column next to your first name in column A.

=IF(ISNUMBER(SEARCH(" and ",A1)),2,1)

Copy this formula down to each cell in your helper column for each name in
column A. Then, when you sort, sort by Column B first, then by Column A.

HTH,
Elkar
 
Works perfectly. Thanks!

Elkar said:
Use a helper column.

Insert a new column next to your data. For this example, I'll assume your
data is in column A and the helper column is column B. Type this formula
into the cell in the helper column next to your first name in column A.

=IF(ISNUMBER(SEARCH(" and ",A1)),2,1)

Copy this formula down to each cell in your helper column for each name in
column A. Then, when you sort, sort by Column B first, then by Column A.

HTH,
Elkar
Click to expand...
 
Back
Top