Top 13 Text Functions in Excel (With Examples)
Excel text functions provide a way to clean, format, extract, and manipulate text strings. If you’re interested in data cleaning or otherwise wrangling data in Excel, this article is for you!
Text functions can reference a string of characters enclosed in quotation marks or they can reference text contained in a cell. LEN(“Excel Club”) and LEN(A5) are both valid functions.
13 Excel Functions for Working with Text Strings
Here are 13 functions I find useful when working with text 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".
Work Efficiently with Excel Text Functions
Text functions are often a much more efficient way to update textual data in an Excel worksheet than manipulating data stored in individual cells. If you experiment with the functions in this article, you’ll be an expert at working with text strings in no time!