To extract text in Excel, use functions such as LEFT, RIGHT, MID, and FIND to return the characters you need from a cell; in Microsoft 365, TEXTBEFORE and TEXTAFTER can make delimiter-based extraction simpler. Start with the source text in a cell, enter a formula in another cell, and adjust the character count or delimiter to match your data.
What You’ll Need
- A workbook in Excel for Microsoft 365 on Windows, with the text you want to work with in cells.
- An empty column or cell for the formula results. Keep the source data unchanged until you have checked the output.
- A clear idea of the part to extract: a fixed number of characters, the text before or after a separator, or text between separators.
The examples below use commas between formula arguments, as Excel for Windows normally does with US-style regional settings. Some regional settings use semicolons instead; if Excel rejects a formula with commas, replace the argument separators with semicolons. TEXTBEFORE and TEXTAFTER require Microsoft 365 or Excel 2024. The classic functions used here, including LEFT, RIGHT, MID, FIND, SEARCH, LEN, and IFERROR, are also available in older desktop editions.
Step-by-Step
- Check the source text and choose a pattern. Look at a few rows before writing a formula. Decide whether the part you want always has the same length or is separated by a character such as a hyphen, space, comma, or @ sign. For example, if A2 contains
AB-104, the hyphen divides a prefix from a number. If the source isreport.final.xlsx, the periods divide the filename from its extension. Knowing the pattern helps you choose a reliable formula rather than counting characters by eye. - Extract a fixed number of characters from the left. LEFT returns characters starting at the beginning of the cell. With the text
INV-583in A2, the first three characters are the prefix. With the textINV-583in A2, enter=LEFT(A2,3)in B2. The result isINV. Change 3 to the number of characters you need. If you omit the character-count argument, LEFT returns one character, so include it when you need more than one. - Extract characters from the right. RIGHT counts from the end of the text, which is useful for a fixed-length suffix, code, or file extension. With
INV-583in A2 and a three-character code at the end, enter=RIGHT(A2,3)in B2. The result is583. This works well when every value ends with the same number of characters. If the suffix length varies, use a delimiter-based formula instead of assuming a fixed count. - Extract a fixed section from the middle. MID takes a starting character position and the number of characters to return. With
SKU-482-BLin A2, where the three-digit code begins at position 5, enter=MID(A2,5,3)in B2. The result is482. MID counts every character, including spaces and punctuation, from the first character as position 1. This method is appropriate for consistently formatted strings; if a prefix can vary in length, find the separator first instead of hard-coding the starting position. - Return everything before a delimiter with TEXTBEFORE. TEXTBEFORE looks for a chosen character and returns the text before its first occurrence. With
AB-104in A2, enter=TEXTBEFORE(A2,"-")in B2. The result isAB. Replace the hyphen in the formula with the separator in your data, such as a space or comma. This is convenient when the prefix has a variable length but the separator is consistent. TEXTBEFORE is available in Microsoft 365 and Excel 2024, not in every older Excel version. - Return everything after a delimiter with TEXTAFTER. TEXTAFTER returns the characters after the specified separator. With
AB-104in A2, enter=TEXTAFTER(A2,"-")in B2. The result is104. If the cell contains several hyphens, the default is to return text after the first one. For example, withSKU-482-BLin A2, enter=TEXTAFTER(A2,"-",2)in B2 to returnBL, the text after the second hyphen. The instance number can help when you need a later occurrence of a repeated separator. - Use FIND with LEFT or MID when newer functions are unavailable. FIND returns the position of a specified character and is case-sensitive. With
AB-104in A2, enter=LEFT(A2,FIND("-",A2)-1)in B2 to returnAB. The minus one excludes the hyphen itself. To return the text after the first hyphen, with the same A2 value, enter=MID(A2,FIND("-",A2)+1,LEN(A2))in B2; it returns104. LEN supplies a long enough character count to cover the rest of the cell. These formulas are useful in older Excel releases and when you want to build the extraction from classic functions. - Extract text after a label or symbol. The same approach works for email addresses, labels, and other consistently separated text. With
sam@example.comin A2, enter=TEXTAFTER(A2,"@")in B2 to returnexample.com. In older Excel, with that same value in A2, use=MID(A2,FIND("@",A2)+1,LEN(A2))to return the same domain. If your goal is to keep only the username, use=TEXTBEFORE(A2,"@")with that A2 value; the result issam. Pick the separator that marks the boundary you want, not necessarily the separator that appears most often. - Fill the formula down and verify results. Select the formula cell and use the fill handle—the small square at the lower-right corner of the selection—to copy the formula to the rows below. You can also select the formula cell and press Ctrl+C, select the destination range, then press Ctrl+V. Excel adjusts a relative reference such as A2 to A3 as it fills down. Check the first, middle, and last results against their source cells, especially if the source text is not perfectly consistent. If you later need fixed text rather than formulas, copy the results and choose Home > Paste > Paste Values.
When a formula will be used on many rows, put it in the first result row beside the matching source row, then fill down only as far as the data goes. If the source data is formatted as an Excel table, entering a formula in a table column usually fills the formula through that column automatically. Keep the extracted results in a separate column so you can compare them with the original values and revise the formula if the pattern changes.
Common Mistakes
Counting the separator as part of the answer. A FIND-based formula returns the separator’s position. To exclude it from the text before the separator, subtract 1 from that position. To start after the separator with MID, add 1 to the position. Leaving out either adjustment can include punctuation or omit the first character you wanted.
Assuming every value has the same format. A formula such as =MID(A2,5,3) works only when the target always starts at character 5. Extra spaces, longer prefixes, or missing separators can make it return the wrong section. Use a delimiter-based formula for variable-length parts, and inspect exceptions rather than assuming one example represents the whole column.
Using the wrong matching behavior. FIND is case-sensitive, while SEARCH is not. If case should not matter, SEARCH can locate a character or text fragment regardless of capitalization. With Order-24 in A2, enter =SEARCH("-",A2) in B2 to return 6, the hyphen’s position. SEARCH also supports wildcard characters in its search text, so use it carefully if you need a literal wildcard character.
Expecting an extraction formula to change the source cell. These formulas return results in the cells where you enter them; they do not remove text from the original value. If you need to replace the original data, first verify the output, then copy the results and paste values over the original cells only if that is what you intend.
Forgetting that separators can be missing or repeated. FIND returns an error when it cannot find the requested text. TEXTBEFORE and TEXTAFTER can also return an error if the delimiter is absent. A formula that works on most rows may therefore fail on a blank or differently formatted row.
Tips
Use a visible separator in your sample data to make formulas easier to read. For instance, if a code has a prefix and number joined by a hyphen, refer to the hyphen in the formula rather than hard-coding the prefix length. If the separator might appear more than once, decide which occurrence marks the boundary. TEXTAFTER accepts an instance number for this purpose; classic formulas may need additional FIND logic to locate a later occurrence.
Wrap a delimiter-based extraction in IFERROR when a missing separator should produce a blank instead of an error. With text values in A2 and a hyphen expected in each value, enter =IFERROR(TEXTBEFORE(A2,"-"),"") in B2. If A2 contains AB-104, the result is AB; if it has no hyphen, the result is blank. Use a different fallback, such as "Check format", if you want to flag records that need attention. Returning a blank can make exceptions less visible, so choose the fallback deliberately.
When a formula needs to be reused, keep its assumptions visible in a nearby heading or note, such as “text before first hyphen.” This makes it easier for someone else to understand why a particular delimiter or character count was used. If you receive new data regularly, test the formula against edge cases: a blank cell, a value without a separator, a value with two separators, and a value with leading or trailing spaces.
Do not remove spaces automatically unless you know they are unwanted. A leading space may be part of the source text or may be an accidental import issue. If you intend to clean whitespace as well as extract text, test a separate cleanup approach on a copy first and compare the results. That keeps extraction logic separate from data-cleaning decisions.
Troubleshooting
You see #VALUE!. A FIND or SEARCH formula may be looking for a delimiter that is not present in that row, or a position/count may be invalid. Check the source cell character by character and confirm the formula uses the same punctuation. If a missing delimiter is an expected case, use IFERROR with an appropriate fallback.
You see #NAME?. Your Excel version may not recognize TEXTBEFORE or TEXTAFTER. Use the classic alternatives with LEFT, MID, FIND, and LEN, or open the file in Microsoft 365 or Excel 2024. Also check that function names are spelled correctly and that text delimiters are enclosed in straight double quotation marks.
The formula appears as text instead of calculating. Select the cell and check Home > Number Format. If it is set to Text, change it to General, then re-enter the formula by pressing F2 and Enter. Also check that the formula begins with an equals sign and that Show Formulas is not enabled. On Windows, Ctrl+` toggles formula display; on Mac, use Control+` where that key combination is available, or turn off Show Formulas from the Formulas tab.
The result is off by one character. Review whether the separator itself should be included. In classic formulas, subtract one to stop before a delimiter, or add one to begin after it. Also remember that spaces count as characters. If the issue occurs only in some rows, compare those source values for extra spaces or a different separator such as an en dash instead of a hyphen.
The formula does not fill correctly. Make sure you filled from the formula cell and that the source reference points to the matching row. A reference like A2 is relative and should update as it is copied down. A reference like $A$2 is absolute and stays fixed; remove the dollar signs if each result should use its own row’s source value.
Frequently Asked Questions
What is the easiest formula to extract text before a character?
In Microsoft 365 or Excel 2024, TEXTBEFORE is usually the clearest choice. With the source text in A2 and a hyphen as the separator, use =TEXTBEFORE(A2,"-"). In older versions, combine LEFT and FIND.
How do I extract text after a character in older Excel?
Use MID with FIND to locate the separator, then start one character after it. With AB-104 in A2, enter =MID(A2,FIND("-",A2)+1,LEN(A2)) to return 104.
Can I extract text between two delimiters?
Yes. With SKU-482-BL in A2, use =TEXTBEFORE(TEXTAFTER(A2,"-"),"-") in Microsoft 365 or Excel 2024. It returns 482, the text between the first and second hyphens. If delimiters are missing or occur in a different order, add error handling and check those rows.
How can I extract the last word in a cell?
In Microsoft 365 or Excel 2024, with a sentence in A2 and spaces separating its words, use =TEXTAFTER(A2," ",-1). It returns the text after the final space. If some cells have trailing spaces, clean or account for them first so the last extracted item is not blank.
Can I use these formulas in Excel for the web or on Mac?
Most classic text functions work in Excel for the web and Excel for Mac, and Microsoft 365 versions generally support TEXTBEFORE and TEXTAFTER. Interface details and keyboard shortcuts can differ, but the formulas themselves are usually the same. If a function is not recognized, check the Excel version and update it or use the classic alternative.
Related
- How to Divide Every Cell in Excel by a Number: Steps
- How to Exit Group Edit Mode in Excel and Ungroup Sheets
- How to Encrypt a Microsoft Excel File with a Password
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Edit a Custom Sort List in ExcelTo edit a custom sort list in Excel for Microsoft 365 on Windows, open File > Options >…
- Excel Formula for Age at a Certain Date Using DATEDIFTo calculate someone’s age on a particular date in Excel, use =DATEDIF(A2,B2,"Y"), where A2 contains the birth date…
- How to Forecast Dates in Excel with Useful FormulasTo extend a regular date schedule, add the interval to the previous date—for example, with a date in…
- Calculating Hours Worked in Excel: Formulas & TipsTo calculate hours worked in Excel, put a shift’s start time in A2 and end time in B2,…
- Excel Not Equal to Blank: Formulas, Examples, and FixesTo test whether a cell is not blank in Excel, use the not-equal operator with an empty string:…
- Excel FILTER and UNIQUE: Remove Duplicate Rows EasilyExcel’s FILTER function does not remove duplicates by itself; combine it with UNIQUE to return distinct rows that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.