As Math notes, you might have more flexibility if you convert your data to individual columns. That can easily be achieved using the
Text to Columns command in the
Data tab, with the data type set to
Delimited and the delimiter set to
That being said, it is not at all difficult to get counts of clients by street or postcode, and to filter for clients on particular streets, postcode codes or surnames without converting the data to columns.
Say that you have your data in the range A2:A500 in Sheet1 and the same range in Sheet2 and both sheets are in the same workbook.
To count the number of clients in postcode "NW12 1AB" in Sheet1, use the formula:
=COUNTIF(Sheet1!$A$2:$A$500, "*NW12 1AB*")
To count the number of "Smiths" in Sheet1 and Sheet2, use:
=COUNTIF(Sheet1!$A$2:$A$500,"*Smith*") + COUNTIF(Sheet2!$A$2:$A$500,"*Smith*")
To count the number of "Grimes" in postcode "NW12 1AB", use:
=COUNTIFS(Sheet1!$A$2:$A$500,"*Grimes*", Sheet1!$A$2:$A$500,"*NW12 1AB*")
To count the number of clients in Sheet2 who are named "Salton" or who live on "High" street, use:
=COUNTIF(Sheet2!$A$2:$A$500,"*Salton*") + COUNTIF(Sheet2!$A$2:$A$500,"*High Street*") -
COUNTIFS(Sheet2!$A$2:$A$500,"*Salton*", Sheet2!$A$2:$A$500,"*High Street*")
To filter Sheet1 to get the Smiths who live in postcode "NW12 1AB",
Highlight the data range A2:A500 and select
Sort & Filter-->
Filter on the
Home tab of the ribbon,
Custom Filter from the dropdown at the top of the column.
Then set the input boxes to
Contains "Smith" And
Contains "NW12 1AB".
You are limited to setting two conditions using this method.
If you want to filter the list and have more than two conditions (or a combination of And and Or conditions), highlight the data range and press
Advanced on the
Sort & Filter section of the data tab.
You will be prompted for the list range and a criteria range.
Advanced filter creates a copy of the filtered list, you will also be prompted for where you want the new, filtered list to be written.
Here is an example of a criteria range that filters the list for all Smiths that live on "High Street":
Note that using the "*Smith*" as the pattern for the surname Smith may not be the best choice: As one commenter notes, it will match a surname like Smithson (or like Smith-Jones) and a street address like Smithsonian Way.
A better pattern would be "* Smith, *, *, *". Even that would give what might be considered a false positive for the name "Donald Smith, Jr." (That name would also cause problems for a Text-to-Column conversion using the comma as a delimiter.)
The fact that Excel recognizes only two wildcards, "*" and "?", with no semantics of the kind available with regular expressions, means that the results of this kind of matching need to be inspected for special cases.