Microsoft Office Tutorials and References

In Depth Information

The formula that follows returns the last ten characters from cell A1. If A1 contains fewer than ten characters,

the formula returns all of the text in the cell.

=RIGHT(A1,10)

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

2. In other words, it returns characters 2 through 6.

=MID(A1,2,5)

The following example returns the text in cell A1, with only the first letter in uppercase (sometimes referred to

as
sentence case
). It uses the LEFT function to extract the first character and convert it to uppercase. This char-

acter then concatenates to another string that uses the RIGHT function to extract all but the first character (con-

verted to lowercase).

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

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

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 may need to convert the asterisks to some other character.

You could use Excel's Home
⇒
Editing
⇒
Find & Select
⇒
Replace command 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) that you

want to replace 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 that you want to replace but not the actual text.

The following formula uses the SUBSTITUTE function to replace 2012 with 2013 in the string
2012 Budget
.

The formula returns
2013 Budget
.

=SUBSTITUTE(“2012 Budget”,”2012”,”2013”)

The following formula uses the SUBSTITUTE function to remove all spaces from a string. In other words, it re-

places all space characters with an empty string. The formula returns
2013OperatingBudget
.

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

The following formula uses the REPLACE function to replace one character beginning at position 5 with noth-

ing. In other words, it removes the fifth character (a hyphen) and returns
Part544
.

=REPLACE(“Part-544”,5,1,””)

You can, of course, nest these functions to perform multiple replacements in a single formula. The formula that

follows demonstrates the power of nested SUBSTITUTE functions. The formula essentially strips out any of