Excel has several functions for extracting text from a string. Use LEFT, RIGHT, or MID to return characters by position; in Microsoft 365 or Excel 2024, use TEXTBEFORE and TEXTAFTER to extract text around a delimiter. The right formula depends on whether you know the character position, the number of characters, or a separator such as a hyphen or comma.
Choose a function based on the text pattern
First decide what part of the string you need and what makes it identifiable. A fixed number of characters is usually simplest to extract by position. A variable-length part is often easier to identify by a delimiter or a recognizable pattern. For example, a product code may always begin with three characters, while a person’s first name may appear before a space even when names have different lengths.
LEFTreturns characters from the beginning of a string.RIGHTreturns characters from the end.MIDreturns characters starting at a position you specify.TEXTBEFOREandTEXTAFTERreturn text on one side of a delimiter.TEXTSPLITdivides a string into separate cells using one or more delimiters.
These functions return text, even when the extracted characters look like a number or date. If you need to calculate with a numeric result, you may need to convert it after extraction. Also, formulas generally use a cell reference rather than altering the source text, so the original value remains available.
Extract characters from the left or right
Use LEFT when the needed text starts at the beginning of a cell. Its basic syntax is LEFT(text, [num_chars]). The second argument is optional; if you omit it, Excel returns one character. RIGHT works the same way from the end of the string.
With a product code in A2 containing ABC-7841, to return the first three characters, enter =LEFT(A2,3). The result is ABC. To return the four characters after the hyphen, and assuming those four characters are at the end of the same value in A2, enter =RIGHT(A2,4). The result is 7841. These are dependable choices when the character count is consistent across your data.
The number argument is a count, not a position. For instance, LEFT(A2,3) means return three characters from the left, not return the character at position three. If the requested count is greater than the string length, Excel returns the whole string. A negative count causes a #VALUE! error, so check calculations that supply the count dynamically.
Return text from a position with MID
MID is useful when the text you need is inside a string rather than at either end. Its syntax is MID(text, start_num, num_chars). The first character is position 1, and the function returns the requested number of characters beginning at the start position.
With an eight-character code in A2 such as AB123XYZ, and with the desired three digits in positions 3 through 5, enter =MID(A2,3,3). The result is 123. This method is appropriate for fixed-format identifiers, such as a code in which the region, year, and sequence always occupy known positions.
If the starting position is zero or negative, or the character count is negative, MID returns #VALUE!. If the requested section extends past the end of the string, Excel returns only the characters that remain. When spaces or punctuation can vary, position-based extraction may become fragile; a delimiter-based formula is usually easier to maintain in that case.
Extract text before or after a delimiter
In Microsoft 365 and Excel 2024, TEXTBEFORE and TEXTAFTER can locate a separator for you. A delimiter is a character or text sequence that marks a boundary, such as a space, comma, slash, or hyphen. These functions are not available in every older Excel release; for an older version, see the alternatives below.
With an email address in A2 such as maya.chen@example.com, enter =TEXTBEFORE(A2,"@") to return maya.chen. The formula returns everything before the first at sign. With the same value in A2, enter =TEXTAFTER(A2,"@") to return example.com. These formulas adapt if the parts before or after the delimiter have different lengths.
For a value such as North-Region-42, the default is to use the first matching delimiter. With that text in A2, =TEXTBEFORE(A2,"-") returns North, while =TEXTAFTER(A2,"-") returns Region-42. The optional instance number can select a later occurrence: with the same A2 value, =TEXTAFTER(A2,"-",2) returns 42. This is handy when a separator appears more than once.
By default, these functions return an error if the delimiter is not found. Their optional arguments can change which delimiter occurrence is used, whether matching proceeds from the end, and what to return if no delimiter exists. When a missing separator is a normal possibility, use the function’s if_not_found argument or handle the error deliberately rather than allowing an unexpected error to spread through a report.
Split a string into multiple cells
Use TEXTSPLIT when a cell contains several pieces that should become separate results. It is available in Microsoft 365 and Excel 2024. Unlike TEXTBEFORE or TEXTAFTER, which return one side of a delimiter, TEXTSPLIT can split a string into columns or rows. Its result spills into neighboring cells, so those cells need to be empty.
With a comma-separated list in A2 containing red,blue,green, enter =TEXTSPLIT(A2,","). The result spills across three cells: red, blue, and green. If the delimiter is a space, use a space as the delimiter argument. For a tab-delimited or more complex file, make sure the delimiter you specify matches the actual character in the cell; a visible space and a nonbreaking space are not always the same character.
TEXTSPLIT also has optional arguments for splitting across rows and for handling consecutive delimiters. For example, two commas in a row can represent an empty item. Decide whether that empty field should be kept or ignored before building a downstream calculation around the split results. If the formula displays a spill error, check whether another value is occupying a cell where the results need to appear.
Older Excel alternatives and helper functions
If your Excel version does not include TEXTBEFORE, TEXTAFTER, or TEXTSPLIT, position-based functions can still extract text, but you may need to locate the delimiter first. FIND returns the position of text within another string and is case-sensitive. SEARCH is the similar case-insensitive option. LEN returns the number of characters in a string.
With an email address in A2 such as maya.chen@example.com, enter =LEFT(A2,FIND("@",A2)-1) to return maya.chen. FIND locates the at sign; subtracting one excludes it from the result. To return the text after the at sign, with the same A2 value, enter =MID(A2,FIND("@",A2)+1,LEN(A2)). The result is example.com. The length argument is deliberately large enough to cover the remaining characters.
These formulas assume the delimiter exists. If it does not, FIND returns an error. You can use IFERROR to provide a fallback, but choose a fallback that makes sense for the data rather than hiding a data-quality problem. In older workbooks, another common approach is to use the Text to Columns command from the Data tab for a one-time split. That changes the selected data rather than creating a formula that recalculates when the source changes.
Handle spaces, case, and messy source text
Many extraction problems are actually data-cleaning problems. A cell may contain leading spaces, trailing spaces, repeated spaces, line breaks, or punctuation that differs from one row to another. A formula can be logically correct and still return an unexpected result if the source text is inconsistent.
TRIMremoves ordinary leading and trailing spaces and reduces repeated internal spaces to single spaces. It does not remove every special whitespace character.CLEANremoves many nonprinting characters, but it does not remove all Unicode characters that may appear in copied data.SUBSTITUTEreplaces specified text, which can help standardize a known separator before extraction.FINDdistinguishes uppercase and lowercase;SEARCHdoes not.
For example, if a name contains irregular ordinary spaces, you can first normalize it with TRIM and then apply a delimiter-based extraction. Be aware that combining cleanup and extraction in one long formula can make a workbook harder to audit. A helper column that shows the cleaned text can make it easier to spot unusual source values.
Case matters most with FIND and SEARCH, not with LEFT, RIGHT, or MID, which simply return the characters at the specified positions. Excel’s newer delimiter functions also have optional case-related behavior in their arguments, so consult the function’s argument hints if the delimiter itself may appear in different letter cases.
Common errors and ways to troubleshoot
Start by checking the exact contents of the source cell. A delimiter that looks like a hyphen could instead be an en dash or another character. A formula searching for a regular space will not find a nonbreaking space. Copying the suspected delimiter from the cell into the formula or inspecting the data with helper functions can help identify the mismatch.
#VALUE!: Often indicates an invalid character count or start position, or that a search function could not find its target.#NAME?: May indicate that the Excel version does not recognize a newer function such asTEXTBEFOREorTEXTSPLIT, or that a function name was mistyped.- Wrong part returned: Check whether the formula uses the first delimiter or a later occurrence, and whether the position count starts at 1.
- Unexpected blank result: Check for empty source cells, consecutive delimiters, or a delimiter at the very beginning or end.
#SPILL!: ForTEXTSPLIT, clear cells in the area where Excel needs to place the results.
Also check whether the source value is actually text. Excel may store dates and numbers as numeric values with formatting applied. Extracting characters from a displayed date can produce results that do not match what you see because the underlying value is a serial number. If you need a specific date display, use TEXT to format the value as text before extracting from that representation, and remember that the output will then be text rather than a date value.
Formula extraction versus other Excel tools
Formulas are best when the extracted result should update automatically as the source changes or when the same rule applies to many rows. Enter the formula beside the first source value, then fill it down the column. In Microsoft 365, dynamic-array functions can return multiple results that spill automatically; in older versions, you may need to copy formulas across or down, or use a different method.
For a one-time task, Excel’s Text to Columns feature may be faster than writing formulas. In Microsoft 365 for Windows, select the cells and choose Data > Text to Columns, then follow the delimiter or fixed-width steps in the wizard. This splits or transforms the selected data, so consider copying the source column first if you need to preserve it. Power Query is another option for repeatable imports and transformations, especially when the same cleanup steps must be refreshed from new files.
The choice is not simply about which method has the shortest formula. Consider whether the source changes, whether missing delimiters should be flagged, how much data is involved, and whether someone else will need to maintain the workbook. A clear two-step formula or helper column is often safer than a compact but difficult-to-understand expression.
Frequently Asked Questions
What is the simplest Excel formula to extract text?
For a fixed number of characters, use LEFT, RIGHT, or MID. For text separated by a delimiter in Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER are often simpler.
How do I extract text between two characters?
In Microsoft 365 or Excel 2024, combine TEXTAFTER and TEXTBEFORE when the delimiters occur in the expected order. The exact formula depends on whether the delimiters repeat and which occurrence marks the boundaries. For older Excel versions, use FIND or SEARCH to locate both positions and MID to return the characters between them.
Can Excel extract text before a space?
Yes. With the text in A2, use =TEXTBEFORE(A2," ") in Microsoft 365 or Excel 2024. In older Excel versions, use LEFT with FIND to locate the first space. If some rows have no spaces, plan for that case so the formula does not return an error.
Why does my extraction formula return an error?
Common causes include a missing delimiter, an invalid character count, a function unavailable in your Excel version, or a delimiter that is not the character you think it is. Check the source cell and the formula arguments, then decide whether a missing match should produce a blank, a message, or an error that makes the issue visible.
Do extracted numbers remain numbers?
Usually not: text-extraction functions return text. If the extracted result should be used in arithmetic, convert it to a number with a suitable conversion function or operation, and check that the result contains only valid numeric characters. Dates need particular care because their displayed form and stored value are different.
Can I extract text in Excel for Mac or the web?
The core functions such as LEFT, RIGHT, and MID are available across current Excel platforms. Availability of newer functions depends on the Excel version and update channel, so check the function autocomplete or Microsoft’s function support information if a formula is not recognized. Ribbon locations and interface details can vary between Windows, Mac, and Excel for the web.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: 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 INDEX MATCH Examples and How to Use ThemIn Excel, INDEX MATCH looks up a value by finding its position with MATCH and returning the value…
- Excel Timesheet Formula: Total Hours, Breaks, and ShiftsFor a timesheet with a start time in B2, an end time in C2, and an unpaid break…
- SUMIF in Excel: Meaning, Syntax, and How to Use ItIn Excel, SUMIF adds numbers only when their matching cells meet one condition; it is a conditional sum.…
- Excel Sum Multiple Columns: Formulas, Examples, and TipsTo sum several columns in Excel, use =SUM(B2:D10) for one continuous block of cells. For separate columns or…
- Excel IF Blank: Formulas for Empty Cells and ResultsIf you want Excel to show one result when a cell is blank and another when it is…
- Excel LET: Can It Use Multiple Variables?Yes. Excel’s LET function allows multiple named values in one formula: write each name and value as a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.