Susan Harkins explains how to use Text To Columns instead of parsing functions and an easy way to average unique values in Excel.
Many users see Excel as a single-method solution -- complex expressions, functions, and even arrays. Expressions can be difficult to write and take hours of testing and troubleshooting if you're not well-versed in syntax and expectations. Often, you can get the same results using an easy-to-implement feature instead. This month, I showed two users how to eliminate complex expressions by using built-in features.
Text To Columns for parsing
Bruce needs to parse a string so he can use the numeric values in other calculations. Unfortunately, he can't control the way the data comes to him -- he's stuck with a long string of values, shown in Figure A, that represent the dimension of a container. Bruce has tried a number of parsing expressions using LEFT(), RIGHT(), MID(), LEN() and FIND(), and he's managed to squeak out the first value using the following expression:
He can't get the function mix just right to parse the second and third dimension values.
Excel imports the three dimensions in one string.
Bruce is working much harder than necessary. Excel's Text To Columns feature will parse the values quickly and easily without expressions. Using Bruce's sheet, let's work through a quick example.
Before you begin, you should decide whether you want to retain the original string. If so, copy the string values and work with the copy, because Text To Columns will write over the original string. Figure B shows the result of copying the original strings in A4:A14 to a new sheet. This is the data set we'll actually parse.
Working with a copy is a good idea.
With a copy in place, engage the Text To Columns feature as follows:
- Select A2:A12 (the appropriate range containing the strings you want to parse).
- Click the Data tab.
- In the Data Tools group, click Text To Columns. In Excel 2003, this feature is found on the Data menu.
- In the resulting dialog, retain the default Delimited option and click Next to continue (we'll use the x character as a delimiter).
- In the Delimiters list, check Other.
- Enter an x in the control to the right (Figure C). Doing so updates the preview below, which omits the x characters and shows the values parsed into three columns.
- Click Next.
- In the next pane, you can specify each columns data type. In this example, Excel defaults to General (Figure D). Retain the default by clicking Finish.
- When (if) Excel asks to confirm that it's writing over the original strings, click OK. Figure E shows the parsed results.
Because Excel set the data type to General, you can use the values in calculations. It is possible to get the same results using expressions, but this method is simple and quick to implement.
Flash Fill for 2013 users
If the string values are consistent in structure, you can try Excel 2013's new Flash Fill feature. In this particular case, I was unable to get Flash Fill to accurately parse all three dimensional values. However, the feature is often useful, so I want to mention it.
First, you need a place to accommodate the three columns of values, so insert three columns to the right of the original strings (in column A). To use Flash Fill to quickly parse the values, do the following:
- To the right of the original strings in B4:B6, manually enter the first three values, 22.1, 11.33, and 113, to set the pattern.
- Select the next blank cell in the column (B7).
- Click the Data tab.
- In the Data Tools group, click Flash Fill (Figure F), and Excel will try to create the list according to the pattern set in step 1.
- To generate the second list (parsing the middle components), manually enter the first three middle values into C4:C6 (with this string set, I was unable to establish a pattern for the second component, and Flash Fill failed).
- Select C7 and click Flash Fill to create the second list of dimensions.
- Repeat this process to create the third list of dimensions in column D.
Unfortunately, Flash Fill doesn't always provide a perfect list, and it fails to live up to this particular task. It's a good feature to try, and that's why I mentioned it. You'll want to check Flash Fill's results carefully for discrepancies. You can learn more about this new feature by reading "Excel 2013 Flash Fill anticipates needs on the fly."
Average unique values
Prakash wants to average a column of values, but only the unique values. In this case, the (almost) expression-less solution is simple -- create a list of unique values using Remove Duplicates. (This feature isn't available in Excel 2003; use the Advanced Filter option mentioned below.)
To illustrate this simple built-in solution, we'll use the simple list shown in Figure G. It's easy to see the duplicates. In the real-world, you might have thousands of rows to consider.
This simple list contains a few duplicates.
If you don't want to permanently remove the original values (and you probably won't), copy the values and create a unique list from the copy (which is what I'll do). Once you have a copy in place, create a list of unique values as follows:
- Select the column of values. In this case, that's B2:B10 (after copying the original list from column A to column B).
- Click the Data tab.
- In the Data Tools group, click Remove Duplicates.
- In the resulting dialog, change the default option by selecting the Continue with the current selection option (Figure H). In this case, the default option would evaluate together both columns A and B when finding duplicates. Because column B is a copy of column A, the result would be the same either way, but this is a contrived example, and that will seldom be the case. You need to understand the difference between the two options.
- Click Remove Duplicates.
- There's nothing to change in the next pane, so click OK.
- When Excel confirms the removed duplicates, click OK. The results are shown in Figure I.
Excel removed the duplicates, which changes the result of the AVERAGE() function in B11. The function in B11 still evaluates B2:B10, but AVERAGE() ignores blank cells.
If you're still using Excel 2003, use the Advanced Filter feature to create a unique list. Read "How do I... return a unique list in Excel?" -- specifically the first section, Manually, the easy way, for specific instructions.
Using a built-in feature in lieu of complicated expressions isn't always possible. However, anytime you're struggling to put a solution in place, take a minute to rethink your strategy. What do you really need to see? How will you use the results? Often, Excel offers features that can provide the results you need without all the hard work! If you've eliminated a set of complex expressions by using a built-in feature, please share your experience in the comments section.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at firstname.lastname@example.org.