Using Excel Text Formulas

This Tech Tip provides helpful formulas to keep in mind when working with text in Excel. PROPER is contributed by Carolyn H. Gould, Director of Institutional Research, Tepper School of Business, Carnegie Mellon University. The remaining sections (CLEAN, TRIM, LEFT and RIGHT, and SUBSTITUTE and REPLACE) are contributed by Angela Henderson, Associate Director of Institutional Research & Effectiveness, Stetson University.

PROPER

As a very popular software application, Excel is useful for sharing data. I often receive Excel data files and share files with others; however, the format in which the data was entered is at times less than desirable.

At my university, I take course evaluation and enrollment information from the university’s system and upload it into the business school’s faculty information system. Once in the faculty information system, the information populates a number of formal reports. Unfortunately, the course titles from the university’s system are originally entered in all capital letters, which doesn’t look very good in a formal report. This is where a simple Excel function comes in handy – PROPER. PROPER converts text in all caps to text in which only the first letter of each word is capitalized.

The following graphic shows eleven courses, the last six of which have been derived from the university’s system. I want to remove all caps from those.

1ttnov2015.JPG
To correct the contents of A7, I enter the following in an empty cell and hit ENTER.

=PROPER(A7)

If one has many cells that require such correction, it is easiest to simply enter the formula for the entire column (even the cells that don’t need correction). In this example, I would type =PROPER(A2) into D2, hit ENTER, then copy down for the entire column. Doing so would have no effect on text that is properly capitalized, but would correct the text in all caps, as shown below.

2ttnov2015.JPG
Once you have created the properly capitalized text, you can paste it (as values, of course) over the old text. Similar functions are available for changing text to all lower case (LOWER) and changing text to all caps (UPPER). All of these provide quick fixes to frustrating formatting issues.

CLEAN

The CLEAN function eliminates nonprintable characters, such as line breaks, from the text selected. In the example below, the original address data contains multiple line breaks per cell. To format the addresses as a single text line, add a CLEAN formula in the next cell, as shown.

3ttnov2015.JPG
TRIM

The TRIM function removes all spaces from text except for single spaces between words. In the example below, the original address contains extra spaces between each element of the address. To remove the extra spaces, add a TRIM formula in the next cell, as shown.

4ttnov2015.JPG

LEFT and RIGHT

The LEFT and RIGHT functions return the first or last characters in a text string, based on the number of characters specified. In the example below, we want to extract just the ZIP Code (the last 5 characters) from the cell containing the complete address. This formula requires both a cell reference and the specification of the number of characters to be extracted. In this case, ZIP Codes are 5 digits, so we would select the cell to reference (T6) and the number of characters to extract (5). The complete RIGHT formula is shown in the text box below.

5ttnov2015.JPG
Just as the RIGHT function returns the last characters in a cell, the LEFT function returns the first characters. If we want to extract just the house number (the first 3 characters) from the cell containing the complete address, we can use the LEFT function. This formula also requires both a cell reference and the specification of the number of characters to be extracted.

6ttnov2015.JPG
SUBSTITUTE and REPLACE

While SUBSTITUTE and REPLACE are similar functions used to replace text within a cell, they rely on different information to do so. Detailed examples of each are provided below.

  • SUBSTITUTE: used to replace specific text existing within a text string.
    • As SUBSTITUTE is based on specific text, it is useful for substituting text in cells with contents of varying length.
  • REPLACE: used to replace any text that occurs in a specific location within a text string.
    • As REPLACE is based on location within a cell, it is useful for replacing text in cells with contents of consistent length and positioning.

SUBSTITUTE

The SUBSTITUTE function replaces specific text within the cell with different specified text. In the example below, the state abbreviation in the original address has been replaced with the complete state name. The SUBSTITUTE formula requires a cell reference (T12), the specific text to be replaced (“CA”), and the replacement text (“California”). The complete formula is shown in the text box below.

7ttnov2015.JPG
REPLACE

While SUBSTITUTE relies on the identification of specific text within a cell to be replaced, the REPLACE function relies on the position of the characters within the cell. In the example below, the state abbreviation in the original address has again been replaced with the complete state name. The REPLACE formula requires a cell reference (T10), the specified start position of text to be replaced (26), the number of characters to replace (2), and the replacement text (“Florida”). The complete REPLACE formula is shown in the text box below.

 
Note: In general, addresses are too variable for the REPLACE formula to be beneficial. This function is most useful in data fields of consistent length and positioning. 

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 5
 
Kristina posted on 11/12/2015 7:09 PM
Great time saving tips Angela and Carolyn -- thanks for sharing!
Terry posted on 11/13/2015 8:17 AM
I love the text functions in Excel. It makes cleaning that messy data so much easier.
Lee posted on 11/13/2015 11:55 AM
Thanks for a helpful and informative summary of some key text functions in Excel! I've used these (and other) text functions frequently, and they can be real time-savers. One small caution with the PROPER function - it returns ALL words with the first letter capitalized, including short words like And, The, Of etc. So you may have to go back and manually change these words, depending on your needs.
Lisa posted on 11/13/2015 11:58 AM
Excellent tips! I love learning new formulas in Excel. I have used TRIM, RIGHT, and LEFT, but the others were new to me. They will be handy for the future.
Christine posted on 11/16/2015 10:02 AM
Great tips - thanks! I am looking forward to putting these to good use!