How to Use the T, TEXT, TRIM, UPPER and VALUE Text Functions to Clean Data in Excel

By Stephen L. Nelson, E. C. Nelson

When you import data into Excel, you may have a few issues. When you import data into Excel, you may find that your text labels don’t look right. The following text functions can help you keep your data clean.

The T function

The T function returns its argument if the argument is text. If the argument isn’t text, the function returns nothing. The function uses the following syntax:

T(value)

For example, the formula T(123) returns nothing because 123 is a value. The formula T(“Seattle”) returns Seattle because Seattle is a text string.

The TEXT function

The TEXT function formats a value and then returns the value as text. The function uses the following syntax:

TEXT(value,format_text)

The value argument is the value that you want formatted and returned as text. The format_text argument is a text string that shows the currency symbol and placement, commas, and decimal places that you want. For example, the formula

=TEXT(1234.5678,"$##,###.00")

returns the text $1,234.57.

Note that the function rounds the value.

The TRIM function

The TRIM function removes extra spaces from the right end of a text string. The function uses the following syntax:

TRIM(text)

The text argument is the text string or, more likely, a reference to the cell holding the text string.

The UPPER function

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

UPPER(text)

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

UPPER("professional")

The function returns the text string PROFESSIONAL.

The VALUE function

The VALUE function converts a text string that looks like a value to a value. The function uses the following syntax:

VALUE(text)

The text argument either supplies the text string that you want to convert or it references the cell holding the text string. For example, to convert the text string $123,456.78 — assume that this isn’t a value but a text string — you can use the following formula:

VALUE("$123,456.78")

The function returns the value 123456.78