Excel 2010 Bible

Chapter 11: Creating Formulas That Manipulate Text

241

The following formula returns the last 10 characters from cell A1; if A1 contains fewer than 10

characters, the formula returns all text in the cell:

=RIGHT(A1,10)

This next formula uses the MID function to return five characters from cell A1, beginning at char-

acter position 2. In other words, it returns characters 2–6.

=MID(A1,2,5)

The following example returns the text in cell A1 with only the first letter in uppercase. It uses the

LEFT function to extract the first character and convert it to uppercase. This then concatenates to

another string that uses the RIGHT function to extract all but the first character (converted to low-

ercase). Here’s what it looks like:

=UPPER(LEFT(A1))&RIGHT(LOWER(A1),LEN(A1)-1)

If cell A1 contained the text FIRST QUARTER, the formula would return First quarter.

Note
This is different than the result obtained using the PROPER function. The PROPER function makes the first
character in each word uppercase. n

Replacing text with other text ..................................................................................

In some situations, you may need a formula 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 choose Home ➪ Editing ➪ Find & Select ➪ Replace to make

the replacement. If you prefer a formula-based solution, you can take advantage of either of two

functions:

l SUBSTITUTE replaces specific text in a string. Use this function when you know the

character(s) to be replaced but not the position.

l 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 2010 with 2011 in the string

2010 Budget. The formula returns 2011 Budget.

=SUBSTITUTE(“2010 Budget”,”2010”,”2011”)

The following formula uses the SUBSTITUTE function to remove all spaces from a string. In other

words, it replaces all space characters with an empty string. The formula returns

2011OperatingBudget.

=SUBSTITUTE(“2011 Operating Budget”,” “,””)