Categories
Excel

Top 13 Text Functions in Excel (With Examples)

Microsoft Excel includes a number of text manipulation functions.

These Excel text formulas are incredibly useful for cleaning, formatting, extracting, and manipulating text strings. If you’re looking for a complete guide to Excel text functions, this post is for you!

As you read the examples below, keep in mind that text functions can reference a string of characters enclosed in quotation marks or they can reference text stored in a cell. LEN(“Excel Club”) and LEN(A5) are both valid.

13 Useful Excel Functions for Working with Text Strings

Here are 13 of the most useful text string manipulation functions in Excel.

1) LEFT: The LEFT function returns the leftmost characters from a text string.

=LEFT("Excel Club", 5) returns "Excel".

2) RIGHT: The RIGHT function returns the rightmost characters from a text string.

=RIGHT("Excel Club", 4) returns "Club".

3) MID: The MID function returns a specific number of characters from a text string, starting at the position specified.

=MID("Excel Club", 3, 3) returns "cel".

4) LEN: The LEN function returns the number of characters in a text string.

=LEN("Excel Club") returns "10".

5) TRIM: The TRIM function removes extra spaces from a text string, leaving single spaces between words.

=TRIM(" Excel Club") returns "Excel Club".

6) CONCATENATE: The CONCATENATE function joins two (or more) text strings into one text string.

=CONCATENATE("Excel", " ", "Club") returns "Excel Club".

7) FIND: The FIND function locates one string of characters within another string and returns the starting position of the text. Arguments used in the FIND function are case-sensitive.

=FIND("Club", "Excel Club") returns "7".

8) REPLACE: The REPLACE function replaces part of a text string with a different text string, based on the number of characters specified.

=REPLACE("Excel Club", 7, 4, "Pub") returns "Excel Pub".

9) SUBSTITUTE: The SUBSTITUTE function replaces occurrences of old text with new text in a text string. Unlike the REPLACE function, the SUBSTITUTE function will replace all occurrences of one text string with another.

=SUBSTITUTE("Excel Club", "Club", "Pub") returns "Excel Pub".

10) LOWER: The LOWER function converts a text string to lowercase.

=LOWER("Excel Club") returns "excel club".

11) UPPER: The UPPER function converts a text string to upper case.

=UPPER("Excel Club") returns "EXCEL CLUB".

12) PROPER: The PROPER function uses capital letters for the first letter in each word of a text string while displaying the remaining letters in lower case.

=PROPER("excel cluB rocks!" returns "Excel Club Rocks!".

13) TEXT: The Excel TEXT function is used to convert a numeric value into a text string using a specified format with format codes.

=TEXT("2/16/24","mmmm d yyyy") returns "February 16, 2024".

Final Thoughts on Excel Text String Formulas

An Excel text function is often a much more efficient way of updating text values than editing individual cells in a worksheet.

If you receive a spreadsheet filled with capital letters, you can use an Excel PROPER function to easily convert it to mixed upper and lower case. Need to revise a text value (or several text values) in your workbook? There are text functions for that too!

If you experiment with the functions in this list, I’m confident you’ll find a text function that will do exactly what you need.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.