Sorting

[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?

 

[Elkar]

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

 

[dleo]

Works perfectly. Thanks!

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