Advanced Text Formulas


The examples in this section are more complex than the examples in the previous section. But, as you'll see, these formulas can perform some very useful text manipulations.

On the CD 

You can access all the examples in this section on the companion CD-ROM in image from book text formula examples.xlsx file.

Counting Specific Characters in a Cell

This formula counts the number of Bs (uppercase only) in the string in cell A1:

 =LEN(A1)-LEN(SUBSTITUTE(A1,"B","")) 

This formula uses the SUBSTITUTE function to create a new string (in memory) that has all the Bs removed. Then the length of this string is subtracted from the length of the original string. The result reveals the number of Bs in the original string.

The following formula is a bit more versatile. It counts the number of Bs (both upperand lowercase) in the string in cell A1.

 =LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"B",""),"b","")) 

Counting the Occurrences of a Substring in a Cell

The formulas in the preceding section count the number of occurrences of a particular character in a string. The following formula works with more than one character. It returns the number of occurrences of a particular substring (contained in cell B1) within a string (contained in cell A1). The substring can consist of any number of characters.

 =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) 

For example, if cell A1 contains the text Blonde On Blonde and B1 contains the text Blonde, the formula returns 2.

The comparison is case sensitive, so if B1 contains the text blonde, the formula returns 0. The following formula is a modified version that performs a case-insensitive comparison:

 =(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),"")))/LEN(B1) 

Expressing a Number as an Ordinal

You may need to express a value as an ordinal number. For example, Today is the 21st day of the month. In this case, the number 21 converts to an ordinal number by appending the characters st to the number.

The characters appended to a number depend on the number. There is no clear pattern, making the construction of a formula more difficult. Most numbers will use the th suffix. Exceptions occur for numbers that end with 1, 2, or 3-except if the preceding number is a 1 (numbers that end with 11, 12, or 13). These may seem like fairly complex rules, but you can translate them into an Excel formula.

The formula that follows converts the number in cell A1 (assumed to be an integer) to an ordinal number:

 =A1&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(A1))={1,2,3}), CHOOSE(RIGHT(A1),"st","nd","rd"),"th")) 

This is a rather complicated formula, so it may help to examine its components. Basically, the formula works as follows:

  1. If the last two digits of the number are 11, 12, or 13, use th.

  2. If Rule #1 does not apply, check the last digit. If the last digit is 1, use st. If the last digit is 2, use nd. If the last digit is 3, use rd.

  3. If neither Rule #1 nor Rule #2 apply, use th.

Cross Ref 

The formula uses two arrays, specified by brackets. Refer to Chapter 14 for more information about using arrays in formulas.

Figure 5-5 shows the formula in use.

image from book
Figure 5-5: Using a formula to express a number as an ordinal.

Determining a Column Letter for a Column Number

This next formula returns a worksheet column letter (ranging from A to XFD) for the value contained in cell A1. For example, if A1 contains 29, the formula returns AC.

 =LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1) 

Note that the formula doesn't check for a valid column number. In other words, if A1 contains a value less than 1 or greater than 16,384, the formula will return an error. The following modification uses the new IFERROR function to display text (Invalid Column) instead of an error value.

 =IFERROR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1),"Invalid Column") 

For compatibility with versions prior to Excel 2007, use this formula:

 =IF(ISERR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)), "Invalid Column",LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)) 

Extracting a Filename from a Path Specification

The following formula returns the filename from a full path specification. For example, if cell A1 contains c:\windows\desktop\myfile.xlsx, the formula returns myfile.xlsx.

 =MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1)) 

This formula assumes that the system path separator is a backslash (\). It essentially returns all the text following the last backslash character. If cell A1 doesn't contain a backslash character, the formula returns an error.

Extracting the First Word of a String

To extract the first word of a string, a formula must locate the position of the first space character, and then use this information as an argument for the LEFT function. The following formula does just that:

 =LEFT(A1,FIND(" ",A1)-1) 

This formula returns all of the text prior to the first space in cell A1. However, the formula has a slight problem: It returns an error if cell A1 consists of a single word. A simple modification solves the problem by using an IFERROR function to check for the error:

 =IFERROR(LEFT(A1,FIND(" ",A1)-1),A1) 

The IFERROR function is new to Excel 2007. For compatibility with previous versions, use this formula:

 =IF(ISERR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1)) 

Extracting the Last Word of a String

Extracting the last word of a string is more complicated because the FIND function only works from left to right. Therefore, the problem rests with locating the last space character. The formula that follows, however, solves this problem. It returns the last word of a string (all the text following the last space character):

 =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) 

This formula, however, has the same problem as the first formula in the preceding section: It fails if the string does not contain at least one space character. The following modified formula uses the IFERROR function to avoid the error value.

 =IFERROR(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)- LEN(SUBSTITUTE(A1," ",""))))),A1) 

For compatibility with previous Excel versions, use this formula:

 =IF(ISERR(FIND(" ",A1)),A1,RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))) 

Extracting All but the First Word of a String

The following formula returns the contents of cell A1, except for the first word:

 =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) 

If cell A1 contains 2007 Operating Budget, the formula returns Operating Budget.

This formula returns an error if the cell contains only one word. The formula below solves this problem and returns an empty string if the cell does not contain multiple words:

 =IFERROR(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),"") 

For compatibility with earlier versions of Excel, use this formula:

 =IF(ISERR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)-FIND(" ",A1,1))) 

Extracting First Names, Middle Names, and Last Names

Suppose you have a list consisting of people's names in a single column. You have to separate these names into three columns: one for the first name, one for the middle name or initial, and one for the last name. This task is more complicated than you may initially think because not every name in the column has a middle name or middle initial. However, you can still do it.

Note 

The task becomes a lot more complicated if the list contains names with titles (such as Mrs. or Dr.) or names followed by additional details (such as Jr. or III). In fact, the following formulas will not handle these complex cases. However, they still give you a significant head start if you're willing to do a bit of manual editing to handle the special cases.

The formulas that follow all assume that the name appears in cell A1.

You can easily construct a formula to return the first name:

 =IFERROR(LEFT(A1,FIND(" ",A1)-1),A1) 

Returning the middle name or initial is much more complicated because not all names have a middle initial. This formula returns the middle name or initial (if it exists). Otherwise, it returns nothing.

 =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND(" ",A1)+1)),"") 

Finally, this formula returns the last name:

 =IFERROR(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)- LEN(SUBSTITUTE(A1," ",""))))),"") 

The formula that follows is a much shorter way to extract the middle name. This formula is useful if you use the other formulas to extract the first name and the last name. It assumes that the first name is in B1 and the last name is in D1.

 =IF(LEN(B1&D1)+2>=LEN(A1),"",MID(A1,LEN(B1)+2,LEN(A1)-LEN(B1&D1)-2) 

image from book
Splitting Text Strings without Using Formulas

In many cases, you can eliminate the use of formulas and use Excel's Data image from book Data Tools image from book Convert Text to Table command to parse strings into their component parts. Selecting this command displays Excel's Convert Text to Columns Wizard (see the accompanying figure), 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'll want to select the Delimited option (in Step 1) and use Space as the delimiter (in Step 2).

image from book

image from book

As you can see in Figure 5-6, the formulas work fairly well. There are a few problems, however-notably names that contain four "words." But, as I mentioned earlier, you can clean these cases up manually.

image from book
Figure 5-6: This worksheet uses formulas to extract the first name, middle name (or initial), and last name from a list of names in column A.

Cross Ref 

If you want to know how I created these complex formulas, refer to Chapter 20 for a discussion of megaformulas.

Removing Titles from Names

You can use the formula that follows to remove four common titles (Mr., Dr., Ms., and Mrs.) from a name. For example, if cell A1 contains Mr. Fred Munster, the formula would return Fred Munster.

 =IF(OR(LEFT(A1,2)={"Mr","Dr","Ms"}),RIGHT(A1,LEN(A1)-(FIND(".",A1)+1)),A1) 

Counting the Number of Words in a Cell

The following formula returns the number of words in cell A1:

 =LEN(TRIM(A1))-LEN(SUBSTITUTE((A1)," ",""))+1 

The formula uses the TRIM function to remove excess spaces. It then uses the SUBSTITUTE function to create a new string (in memory) that has all the space characters removed. The length of this string is subtracted 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) 




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net