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