Excel has built-in string functions—usually called text functions—that let you extract, search, clean, change, combine, and split text in cells. For example, LEFT, FIND, SUBSTITUTE, TRIM, TEXTJOIN, and TEXTSPLIT can automate common tasks such as separating names, fixing imported data, and assembling labels.
What Excel string functions do
A string is a sequence of characters: letters, numbers, spaces, punctuation, or a mixture. In Excel, the term “text function” is more common than “string function,” but people often use the terms to mean the same thing. These functions return or manipulate text values in cells; they do not change the source value unless you replace it with the result.
Text functions are useful when data arrives in a format that is convenient for another system but awkward for analysis. An imported list might put a first and last name in one cell, add spaces around product codes, or store several identifiers in a single delimited field. A formula can create a consistent, reusable result without manually editing each row.
Most text functions take a cell reference or text value as an argument and return a result. As with other Excel formulas, type an equals sign first. You can combine functions—for example, use FIND to locate a separator and MID to extract the text after it. If the source cell changes, the formula result updates automatically.
Extracting characters and portions of text
LEFT, RIGHT, and MID extract parts of a text value. LEFT returns characters from the beginning, RIGHT returns them from the end, and MID returns characters starting at a position you specify. LEN returns the number of characters, including spaces, and is often useful for working out a starting position or checking a value’s length.
With a product code in A2, where A2 contains INV-2047, this formula returns INV:
With the code in A2, use =LEFT(A2,FIND("-",A2)-1).
FIND locates the hyphen at position 4; subtracting 1 tells LEFT to return the first three characters. To extract the four digits at the end, with the same value in A2, use =RIGHT(A2,4). The result is 2047.
MID is handy when you know where a section begins, or can calculate that position. With Maya Chen in A2, use =MID(A2,6,4) to return Chen. The arguments are the source text, starting position, and number of characters. Positions start at 1, not 0. If the structure varies from row to row, a fixed position may not work; locating a delimiter with FIND or SEARCH can make the extraction more adaptable.
LEN counts spaces as characters. With Maya Chen in A2, =LEN(A2) returns 9: four letters, one space, and four letters. An empty-looking cell can also contain spaces or other characters, so LEN can help distinguish a truly empty cell from one that only appears blank.
Finding and replacing text
FIND and SEARCH return the position of one text string inside another. FIND is case-sensitive, while SEARCH is not. Both return an error if the requested text is absent. Use IFERROR if a missing match is an expected possibility and you want a friendlier result rather than an error value.
With an email address in A2, such as maya.chen@example.com, use =MID(A2,FIND("@",A2)+1,LEN(A2)) to return example.com. FIND identifies the at sign, adding 1 starts immediately after it, and LEN supplies a length long enough to return the remaining text. If some rows have no at sign, this formula returns an error for those rows; that may be appropriate if the missing character indicates invalid data.
SEARCH also supports wildcard characters: an asterisk can match a sequence of characters, and a question mark can match one character. FIND does not treat those characters as wildcards. This distinction matters when searching for literal punctuation or when the search pattern should match variable text.
SUBSTITUTE replaces matching text wherever it appears, optionally targeting a particular occurrence. REPLACE instead replaces a specified number of characters beginning at a position. With red, blue, red in A2, =SUBSTITUTE(A2,"red","green") returns green, blue, green. With 555-0188 in A2, =REPLACE(A2,1,3,"212") returns 212-0188. Choose SUBSTITUTE when the text to change is known; choose REPLACE when its character position and length are known.
Cleaning and changing text
TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. With Maya Chen in A2, =TRIM(A2) returns Maya Chen. This is useful for imported names and labels, because extra spaces can cause values that look alike to compare as different.
TRIM does not remove every kind of whitespace. In particular, nonbreaking spaces—often copied from web pages—can remain. CLEAN removes many nonprinting control characters, but it is not a universal cleaner for every Unicode character or unusual whitespace. When imported data still looks inconsistent after TRIM and CLEAN, inspect the actual characters or replace the specific unwanted character with SUBSTITUTE.
UPPER converts text to uppercase, LOWER converts it to lowercase, and PROPER capitalizes the first letter of each word. These are useful for creating a consistent display, but PROPER is not a reliable way to determine a person’s preferred capitalization: names and abbreviations do not always follow its word-by-word pattern. Keep the original data if capitalization has meaning, and use a transformed result only where the desired format is clear.
EXACT compares two text values and returns TRUE only when their characters match exactly, including capitalization. Ordinary equality comparisons in Excel generally do not distinguish uppercase from lowercase. For example, a case-sensitive check can use =EXACT(A2,B2). This is useful for validation, but it does not normalize spaces or other hidden characters; clean the values separately if those differences should not count.
Joining text from multiple cells
CONCAT combines text from multiple references or values. TEXTJOIN adds a delimiter, such as a comma or space, and can be set to ignore empty cells. The ampersand operator is another simple way to join values. These options help build full names, readable descriptions, or export-ready labels from separate columns.
With a first name in A2 and a last name in B2, =CONCAT(A2," ",B2) joins them with a space. The ampersand equivalent is =A2&" "&B2. CONCAT is convenient for a list of values; the ampersand is often clear for a short expression. Neither method automatically adds punctuation or separators—you must include those as text arguments or operators.
With A2 containing North, B2 blank, and C2 containing East, =TEXTJOIN(" / ",TRUE,A2:C2) returns North / East. The first argument is the delimiter, and TRUE tells Excel to ignore empty cells. If you use FALSE instead, Excel includes a delimiter for the blank cell, which can produce consecutive separators. TEXTJOIN is available in Microsoft 365 and newer perpetual Excel versions, including Excel 2019 and later.
One common trap is joining numbers that need a particular display format. A number stored as a value may not retain the way it looks on the worksheet when combined with text. If formatting matters—for example, leading zeroes in an ID—store the identifier as text or format it explicitly as part of the formula. Do not treat values such as account codes as ordinary numbers if their digits and leading zeroes are significant.
Splitting text into separate cells
TEXTSPLIT divides text around a chosen delimiter and returns the pieces into neighboring cells. It is useful for separating a delimited list, such as values divided by commas, pipes, or line breaks. TEXTSPLIT is available in Microsoft 365 and Excel 2024; it is not available in every older Excel release.
With North|East|West in A2, use =TEXTSPLIT(A2,"|"). Excel returns North, East, and West in adjacent columns, provided those output cells are empty. If the delimiter separates rows rather than columns, TEXTSPLIT has a row-delimiter argument; check the function’s argument order before adding optional arguments.
Older Excel versions can split simple, predictable text using Text to Columns on the Data tab, or with combinations of LEFT, MID, RIGHT, FIND, and LEN. Text to Columns changes the selected data, so make a copy first if the original column must be preserved. A formula-based approach keeps the source intact and recalculates when it changes, but can be harder to maintain when delimiters or text lengths vary.
Choosing a function and avoiding errors
Start by identifying the operation: extract by position, locate a marker, replace matching text, standardize casing, remove unwanted characters, join values, or split a field. Then choose a function based on whether the data has a fixed structure. LEFT and MID are straightforward for consistent formats; FIND or SEARCH can make a formula respond to a separator that moves; TEXTSPLIT is concise for delimited values when the Excel version supports it.
- Use FIND for case-sensitive location and SEARCH for case-insensitive location or wildcard patterns.
- Use SUBSTITUTE to change a known sequence of characters and REPLACE to change a known number of characters at a known position.
- Use TRIM for ordinary repeated spaces, and investigate nonbreaking spaces or control characters when those remain.
- Use TEXTJOIN when you need a delimiter and optional handling of blank cells; use CONCAT or ampersands for simpler joins.
- Check that cells where a dynamic result should spill are clear. Existing values can block TEXTSPLIT’s output and cause a spill error.
Other common errors have understandable causes. FIND and SEARCH return errors when there is no match; LEFT, RIGHT, and MID can return unexpected results if the position or character count is wrong. Excel may also interpret a numeric-looking string as a number when data is imported, which can strip leading zeroes. Confirm the cell’s contents and the formula’s assumptions before trying to mask an error with IFERROR.
Availability matters when a workbook will be opened in older Excel versions. Core functions such as LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, REPLACE, TRIM, UPPER, and LOWER have long been part of Excel. Newer functions such as TEXTSPLIT may not calculate in older releases. If collaborators use different versions, favor functions they all support or confirm compatibility before sharing.
Frequently Asked Questions
What is the difference between a string function and a text function in Excel?
They generally refer to the same kind of operation: working with text values. Excel’s function names and documentation commonly use the word “text,” while programmers often call a sequence of characters a string.
Which Excel function extracts text before a character?
In Microsoft 365 or Excel 2024, TEXTBEFORE can return text before a specified delimiter. In older versions, combine FIND with LEFT, subtracting one from the delimiter position so the delimiter itself is excluded.
Can Excel string functions be case-sensitive?
Yes. FIND is case-sensitive, SEARCH is not, and EXACT compares two text values with case sensitivity. Pick the function that matches the comparison you intend to make.
Why does TRIM not remove all spaces?
TRIM handles ordinary spaces, not every whitespace character. Text copied from websites or other systems may contain nonbreaking spaces or other characters that need to be identified and replaced separately.
Are Excel text functions available in Google Sheets?
Many familiar names, such as LEFT, MID, LEN, FIND, and SUBSTITUTE, also exist in Google Sheets, but availability and behavior can differ by product. Check the function documentation for the spreadsheet app and version you are using rather than assuming every Excel function is shared.
Related
- Graphing the Line y = -1: Points, Slope, and Examples
- Google Sheets SPLIT Function: How to Separate Text
- Split Character Array in MATLAB: Methods and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Calendar Days in a MonthThe simplest Excel formula for calendar days in a month is =DAY(EOMONTH(A1,0)), where A1 contains any date within…
- Excel Formula for Listing Items: 5 Real MethodsThe fastest way to list items in Excel is to use the =UNIQUE(range) formula to extract distinct values,…
- Excel Formulas With Text: How to Combine Words and ValuesTo use text in an Excel formula, put literal words inside straight double quotation marks, such as ="Ready".…
- Excel COUNTIF for Text: Exact and Partial MatchesTo count cells that match text in Excel, use COUNTIF: with the text values in A2:A10, =COUNTIF(A2:A10,"Apple") counts…
- Excel Formula for Lookup and Match: Top MethodsThe core Excel formula for lookup and match is =INDEX(range, MATCH(lookup_value, lookup_array, 0)), which returns a value at…
- Excel Filter Table by Keyword: Steps, Options, and TipsTo filter an Excel table by keyword, open the filter arrow in the column you want to search,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.