Filtering Cells in Microsoft Excel

Contact   |   Help Topics   |   Excel 2000

Awhile back I was asked how to separate items in a cell such as names or addresses. My old solution was to save as text, then re-import into Excel, choosing the correct delimiter (e.g. a space or comma) to separate the items. Another option was to use the Text to Columns... option from the Data menu.

Now I've figured out a much more powerful and flexible way of doing it. Say you want to separate City, State and Zip. Here's the code; the explanation follows (NOTES: this tutorial assumes a basic knowledge of Microsoft Excel formulas. Also, the width of your window may wrap the formulas onto multiple lines, while they should actually be on only one line):

Example 1:

Brief Explanation:

Detailed Explanation:

• "text"   is the text string containing the characters you want to extract (e.g. A1).
• "start_num" is the position of the first character you want to extract in text (in cell B1, "San Anselmo," this is just 1). The first character in text has start_num 1, and so on. In cells C1 and D1, the FIND function is used to determine "start_num" (see below for how FIND works) for the state and zip code.
• "num_chars" specifies the number of characters you want MID to return from text. In cell B1, num_chars is determined by the FIND function (because different cities have differrent numbers of characters), which finds the position of the comma, then subtracts 1. In cell C1, the state, num_chars is always 2. In cell D1, num_chars is 10, to allow for a hyphen plus a 4 digit zip extension (e.g. 94960-1234). NOTE: if num_chars is larger than the actual number of characters, then Excel just returns whatever's there.

Formula Syntax: FIND(find_text,within_text,start_num)

• "find_text" is the text you want to find. For example, we're looking for a comma, ",". If you were separating first name and last name, you'd just look for a space, " " (NOTE: always enclose text with quotes, while cell references have no quotes).
• "within_text" is the text containing the text you want to find (e.g. A1).
• "start_num" specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1. I omitted start_num for all of my formulas because I always started from the beginning of the string.

Example 2:

Example 3:

Note that hyphens and spaces are ignored. The only thing needed in your data is a properly-spaced comma. The number 30 found in the formulas may be increased if your list of names conatins names that are longer than 30 characters (unlikely).
 
  Back to help topics