Excel Text Function Videos

Short videos show how to use Excel Text functions - PROPER, SUBSTITUTE, REPLACE, TRIM and other functions

PROPER Function

When people enter their contact information in online forms, the data you collect might be messy. The Excel file in this video has text in upper, lower, proper and mixed case.

messy data with mixed case

See how to use the Excel PROPER function to clean up the first and last names, and make them consistent.

Download the PROPER function sample file to follow along with the video. The zipped file is in xlsx format, and there are no macros in the file.

NOTE: The PROPER function only works correctly on names that have a single capital letter, at the start of each word in the name. For example, "de Bruin" is the correct spelling, but PROPER would change that to "De Bruin".

ADDRESS Function

The ADDRESS function returns a cell address as text, based on a row and column number. It can return an absolute or relative address, in A1 or R1C1 style. A sheet name can also be included in the result.

In this video you'll see how to use ADDRESS to get a cell address from row and column numbers, find a cell value from row and column numbers, and return the address of the cell with the highest value.

REPLACE Function

The REPLACE function replaces characters within text, based on the number of characters, and starting position, specified.

In this video you'll see how to change area codes in phone numbers, replace the first space with a colon and space, and use nested REPLACE functions to insert hyphens

The Excel SEARCH function looks for a text string, within another text string, and returns its position, if found. Watch this short video to see 3 examples of how to use SEARCH.

For more information, go to the SEARCH function page.


The SUBSTITUTE function replaces characters within text, for all instances, or for a specified occurrence.

In this video you'll see how to use SUBSTITUTE to change a region name in a report title, remove non-printing characters, and substitute a space character

TEXT Function

The TEXT function converts a date or number to text, and lets you specify the formatting by using special format strings.

Watch this short video to see the steps for combining text and formatting the results, using the TEXT function.

TRIM Function

The TRIM function removes all the spaces in a text string, except for single spaces between words.

In this video you'll see how to use TRIM to remove spaces from the start and end of a text string, remove all except single spaces between words, and you'll see that TRIM does NOT remove some special space characters that have been copied from websites.

Excel Function Tutorials

Sum Functions

EXACT Function

VLOOKUP Function

INDEX / MATCH Functions

Count Functions



About Debra


Last updated: November 8, 2022 8:01 PM