How to Use the LEN, LOWER, MID and PROPER Text Functions to Clean Data in Excel

By Stephen L. Nelson, E. C. Nelson

Importing data into Excel can be tricky. One problem you may find when you import data into Excel is that your text labels don’t look right. You can help keep your data clean with these text functions.

The LEN function

The LEN function counts the number of characters in a text string. The function uses the following syntax:

LEN(text)

The text argument either supplies the text string that you want to measure or references the cell holding the text string. For example, to measure the length of the text string in cell I81, use the following formula:

LEN(I81)

If cell I81 holds the text string Semper fidelis, the function returns the value . Spaces are counted as characters, too.

The LOWER function

The LOWER function returns an all-lowercase version of a text string. The function uses the following syntax:

LOWER(text)

The text argument either supplies the text string that you want to convert or references the cell holding the text string. For example, to convert the text string PROFESSIONAL to professional, use the following formula:

LOWER("PROFESSIONAL")

The function returns professional.

The MID function

The MID function returns a chunk of text in the middle of text string. The function uses the following syntax:

MID(text,start_num,num_char)

The text argument either supplies the text string from which you grab some text fragment or it references the cell holding the text string. The start_num argument tells Excel where the text fragment starts that you want to grab. The num_char argument tells Excel how long the text fragment is. For example, to grab the text fragment tac from the text string tic tac toe, use the following formula:

=MID("tic tac toe",5,3)

The function returns .

The PROPER function

The PROPER function capitalizes the first letter in every word in a text string. The function uses the following syntax:

PROPER(text)

The text argument either supplies the text string or references the cell holding the text string. For example, to capitalize the initial letters in the text string ambassador kennedy, use the following formula:

PROPER("ambassador kennedy")

The function returns the text string Ambassador Kennedy.