Page 264 - Excel 2007 Bible

17_044039 ch12.qxp 11/21/06 11:05 AM Page 221
Creating Formulas That Manipulate Text
Splitting text strings without using formulas
n many cases, you can eliminate the use of formulas and use Excel’s Text To Columns command to parse
Istrings into their component parts. This command is found in the Data Tools group of the Data tab. The Text
To Columns command displays the Convert Text To Columns Wizard, which consists of a series of dialog
boxes that walk you through the steps to convert a single column of data into multiple columns. Generally,
you want to select the Delimited option (in Step 1) and use Space as the delimiter (in Step 2), as shown in the
following figure.

The formula uses the TRIM function to remove excess spaces. It then uses the SUBSTITUTE function to 12
create a new string (in memory) that has all the space characters removed. The length of this string is sub-
tracted from the length of the original (trimmed) string to get the number of spaces. This value is then
incremented by 1 to get the number of words.
Note that this formula will return 1 if the cell is empty. The following modification solves that problem:
=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,””))+1)

221