Ms Office Tune Up

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)

Popularity: 4% [?]

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.

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," ",""))))),"")

Read the rest of this entry »

Popularity: 6% [?]

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)))

Popularity: 4% [?]

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))  Read the rest of this entry »

Popularity: 5% [?]

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: Read the rest of this entry »

Popularity: 7% [?]

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: Read the rest of this entry »

Popularity: 5% [?]

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","")) Read the rest of this entry »

Popularity: 5% [?]

You can use the REPLACE function in conjunction with the SEARCH function to replace part of a text string with another string. In effect, you use the SEARCH function to find the starting location used by the REPLACE function.

For example, assume cell A1 contains the text Annual Profit Figures. The following formula searches for the word Profit and replaces those six characters it with the word Loss:

=REPLACE(A1,SEARCH("Profit",A1),6,"Loss")

This next formula uses the SUBSTITUTE function to accomplish the same effect in a more efficient manner:

=SUBSTITUTE(A1,"Profit","Loss")

Popularity: 6% [?]

The Excel FIND and SEARCH functions enable you to locate the starting position of a particular substring within a string:

• FIND: Finds a substring within another text string and returns the starting position of the substring. You can specify the character position at which to begin searching. Use this function for case-sensitive text comparisons. Wildcard comparisons are not supported.

• SEARCH: Finds a substring within another text string and returns the starting position of the substring. You can specify the character position at which to begin searching. Use this function for non–case-sensitive text or when you need to use wildcard characters.

The following formula uses the FIND function and returns 7, the position of the first m in the string. Notice that this formula is case sensitive.

=FIND("m","Big Mamma Thornton",1)

Read the rest of this entry »

Popularity: 5% [?]

In some situations, you may need to replace a part of a text string with some other text. For example, you may import data that contains asterisks, and you need to convert the asterisks to some other character. You could use Excel’s Find and Replace dialog box to make the replacement. If you prefer a formula-based solution, you can take advantage of either of two functions:

• SUBSTITUTE: Replaces specific text in a string. Use this function when you know the character(s) to be replaced, but not the position.

• REPLACE: Replaces text that occurs in a specific location within a string. Use this function when you know the position of the text to be replaced, but not the actual text.

The following formula uses the SUBSTITUTE function to replace 2006 with 2007 in the string 2006 Budget. The formula returns 2007 Budget.

=SUBSTITUTE("2006 Budget","2006","2007")

Read the rest of this entry »

Popularity: 6% [?]