Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm not sure if my description above is accurate.

I have a client's full name and address in a single cell e.g.;
Mr Smith, 1 High Street, London NW12 1AB

I have hundreds of names, address and postcodes in a spreadsheet which I need to be able to search and filter by surname, street name and postcode.

I'm trying to identify and count the number of clients in each street and post code . For example, how many clients do we have in SE12 as well as how many clients we have in NW12 1AB. I think, my problem is searching each cell for 3 different bits of information.

I also need to identify the same information in 2 spreadsheet. For example, how many Ms Smiths do we have in 2 spreadsheets. How many clients in the High Street or same postcode appear in both spreadsheet.

How can I accomplish this?

I'm not a whiz at excel and I won't be offended if you keep the language very simple.

share|improve this question
    
You have all your data in a single cell. Can you make each information in a different column? Let's say, columns: Name, Street and Postal Code. –  Math Aug 2 '13 at 15:14
    
You can use text to columns to parse the cells into different columns and then sort. –  Raystafarian Aug 2 '13 at 16:09
    
@Raystafarian, yes, this is possible, but i want to know if is he ok with that. but if he isn't i can't see a solution –  Math Aug 2 '13 at 16:19

2 Answers 2

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 Comma.

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,

  • Choose Text Filter-->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.

Since 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":

enter image description here

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.

share|improve this answer
    
Note that clients whose name is Smithson or who live on Smithsonian Way will give false positives. –  Scott Aug 3 '13 at 1:14
    
What’s this “AC2”? Typo for “A$2”? –  Scott Aug 3 '13 at 1:14
    
Yes, @Scott, typo for A$2. Yes, "Smith" will match your examples, as well as "Smith-Jones"; "* Smith,*" will fix those but won't catch "Smith, Jr." (nor will Text-to-Columns). Name and address data of this sort usually need multiple passes to catch all the variations. –  chuff Aug 3 '13 at 2:04

In case you don’t want to tamper with your existing data, you can extract the three fields into helper columns with Excel’s string formulas.  Assuming your client’s full name and address are in cell A1, set up

  • B1=FIND(",", A1)
  • C1=TRIM(LEFT(A1, B1-1))
  • D1=FIND(",", A1, B1+1)
  • E1=TRIM(MID(A1, B1+1, D1-B1-1))
  • F1=TRIM(RIGHT(A1, LEN(A1)-D1))

Then, for the example data you provided, you will get the following results:

  • B19
  • C1Mr Smith
  • D124
  • E11 High Street
  • F1London NW12 1AB

The TRIM() function removes spaces from the beginning and end of a string.  Without that function, if your original data has spaces after the commas, then E1 and F1 will have spaces at the beginning, which can corrupt later analysis.  TRIM() will also collapse multiple spaces to single spaces; e.g., if your original data said Mr   Smith, then B1 would still be Mr Smith.

If some A1 values have fewer or more than two commas, that can be handled with more complicated solutions.

share|improve this answer
    
Use the Trim function to trim out any leading or trailing spaces. –  wbeard52 Aug 3 '13 at 1:57
    
@wbeard52: Done. (D’oh!) –  Scott Aug 3 '13 at 20:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.