To extract part of a cell’s text in Excel, use a formula that matches what you need: for example, with a hyphen-separated code in A2, =TEXTBEFORE(A2,"-") returns the text before the first hyphen. Enter the formula in a blank cell and replace A2 and the delimiter with the ones in your data. The guide below covers fixed-position text, text next to a separator, and options for older Excel versions.
What You’ll Need
- Excel for Microsoft 365 on Windows, or another Excel version with formulas such as LEFT, MID, RIGHT, FIND, and LEN.
- A cell containing the original text and a blank cell where you can put the extraction formula.
- If you want to use TEXTBEFORE or TEXTAFTER, Microsoft 365 or Excel 2024. Older versions can use alternatives made with FIND and other standard text functions.
You do not need to change or split the original column. A formula returns the extracted text in a separate cell, leaving the source value intact. The examples use A2 as the source cell and commas between formula arguments, as in a typical US regional setup. If your Excel expects semicolons, use those instead of commas.
Step-by-Step
- Look at the text and decide what portion you want. Identify whether the desired result is a fixed number of characters, a section at the start or end, or text separated by a repeatable character such as a hyphen, comma, space, slash, or at sign. For example, the value
INV-2026-0458contains three parts separated by hyphens. Decide whether you wantINV,2026, or0458before choosing a formula. If the examples in your column use inconsistent separators, inspect several rows first; one formula may not fit every row without extra conditions. - Choose an empty result cell. Click a blank cell beside the first source value, such as B2 when the text to work on is in A2. Keeping the original data in column A makes the result easy to compare and gives you a fallback if the formula needs adjusting. If B2 already contains data, choose a different empty column so you do not overwrite anything.
- Use LEFT or RIGHT for a fixed number of characters at an edge. LEFT counts from the start of the text; RIGHT counts from the end. With the text
PRD-7842in A2 and a need for its first three characters, use the following formula in B2.
With PRD-7842 in A2, this formula returns PRD: =LEFT(A2,3). The number 3 is the number of characters to return. With the same value in A2 and a need for its final four characters, use =RIGHT(A2,4); it returns 7842. These functions count characters, not words, so include punctuation or spaces if they fall within the chosen position range.
- Use MID when the text starts at a known position inside the cell. MID takes a starting position and a character count. Excel numbers the first character as position 1. For example, with
AB-739-Qin A2, the digits begin at position 4. Enter this formula in B2 to return the three digits.
With AB-739-Q in A2, =MID(A2,4,3) returns 739. The second argument is the starting position; the third is how many characters to return. MID is useful when a field always follows a consistent layout, but it can return the wrong portion if some entries have different-length prefixes.
- Use a delimiter-based formula when the position varies. A delimiter is a character that marks the boundary between parts, such as a hyphen or @ sign. In Microsoft 365 and Excel 2024, TEXTBEFORE and TEXTAFTER let you refer to that boundary directly. If A2 contains
INV-2026-0458, enter the formula below to return the text before its first hyphen.
With INV-2026-0458 in A2, =TEXTBEFORE(A2,"-") returns INV. To return everything after the first hyphen from the same cell, use =TEXTAFTER(A2,"-"); the result is 2026-0458. These functions make formulas easier to read than counting character positions. They return text relative to the first matching delimiter by default.
- Extract text between two delimiters if needed. For example, to return the middle part of
INV-2026-0458, use TEXTBEFORE with the result of TEXTAFTER. With that value in A2,=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")returns2026. This works by first removing everything through the first hyphen, then taking the part before the next hyphen. It requires Microsoft 365 or Excel 2024. - Use FIND-based formulas in older Excel versions. FIND locates the position of a delimiter, and LEFT or MID can use that position to return the desired text. If A2 contains
INV-2026-0458, the formula below returns the text before the first hyphen and works without TEXTBEFORE.
With INV-2026-0458 in A2, =LEFT(A2,FIND("-",A2)-1) returns INV. FIND returns the hyphen’s position, and subtracting 1 prevents the hyphen itself from being included. To return the text between the first and second hyphens, use this formula with the same value in A2: =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1). It returns 2026. The nested FIND searches for the second hyphen after locating the first.
- Confirm the result, then fill the formula down. Check the first result against the source cell, including whether the delimiter was included or excluded as intended. If it is correct, select the formula cell and drag its fill handle—the small square at the lower-right corner—down alongside the remaining rows. You can also copy the formula cell, select the destination range, and press Ctrl+V on Windows. Excel adjusts a relative reference such as A2 to A3, A4, and so on as it fills.
- Keep the extracted results or turn them into fixed values. Formula results update when the source text changes. If you need a permanent snapshot, copy the result cells, then choose Home > Paste > Paste Values. In Excel for Mac, the formula functions work in the same way; ribbon labels may be arranged differently, and the keyboard shortcut for copying is Command+C rather than Ctrl+C. Excel for the web also supports the core formulas and TEXTBEFORE/TEXTAFTER when available in the account’s version.
Common Mistakes
Counting characters incorrectly: MID, LEFT, and RIGHT count spaces and punctuation as characters. If the desired text begins after a two-character prefix, account for both characters when setting MID’s starting position. Test the formula on one easily checked example before filling it down.
Using a fixed position for uneven text: A formula such as =MID(A2,4,3) assumes the target always starts at character four. If some values have longer prefixes, the result shifts. Prefer a delimiter-based formula when a reliable separator is present.
Forgetting that FIND is case-sensitive: FIND distinguishes uppercase and lowercase text. If you need a case-insensitive search for a text string, SEARCH is the alternative. For common punctuation delimiters, case usually is not relevant, but it matters when the delimiter is a letter.
Leaving a delimiter in the result by accident: TEXTAFTER returns content after the delimiter, while a FIND-based MID formula needs the starting position adjusted with +1 to skip it. Likewise, subtract 1 from the delimiter’s position when using LEFT to exclude it.
Replacing source data or copying the wrong range: Put extraction formulas in a separate column and check the selected range before pasting values. If you replace the original text with formula results, you may lose information needed to correct a formula later.
Tips
Use a short test row with a result you can verify manually before applying a formula to hundreds of entries. A simple example makes off-by-one errors easier to spot. Keep the source and result columns next to each other, and use a clear header such as Extracted Code so that other people can tell what the new values mean.
If the delimiter occurs more than once, decide which occurrence matters. TEXTBEFORE and TEXTAFTER accept optional instance arguments in supported versions, which can target a later occurrence. For instance, with INV-2026-0458 in A2, =TEXTAFTER(A2,"-",2) returns 0458, the portion after the second hyphen. This is available in Microsoft 365 or Excel 2024. In an older version, a longer FIND formula can locate later occurrences.
When your data contains spaces around the extracted value, you can remove leading and trailing spaces with TRIM. For example, if A2 contains Item: Blue Pen and you want the text after the colon, use =TRIM(TEXTAFTER(A2,":")) in Microsoft 365 or Excel 2024; it returns Blue Pen. TRIM removes extra spaces at the ends and reduces repeated internal spaces to one, so do not use it if repeated spaces are meaningful in your data.
Do not convert a result to a number unless you need to do calculations. Codes such as 0074 may look numeric, but converting them can remove leading zeroes. Text extraction formulas generally return text, which is appropriate for identifiers, postal codes, product codes, and similar labels.
Troubleshooting
The formula shows #VALUE!: A FIND-based formula may return this error when its delimiter is not present in the source text. Check for a different dash character, missing punctuation, or unexpected spaces. A standard hyphen - is not the same as an en dash –. If some rows legitimately have no delimiter, decide what the result should be for those rows and handle them separately or add an error-handling formula.
The formula is displayed instead of a result: Make sure the cell is not formatted as Text. Select it, choose Home > Number Format > General, then edit the formula by pressing F2 and Enter on Windows. On Mac, use Control+U to edit the active cell in many Excel keyboard configurations, or click in the formula bar and confirm the formula. Also check that the formula begins with an equals sign and that Show Formulas is not turned on; press Ctrl+` on Windows to toggle it.
The result is shifted or missing a character: Recheck the starting position, character count, and whether the delimiter should be included. MID starts counting at 1, not 0. Compare the formula’s expected character positions with the actual cell contents, including spaces at the beginning of the text.
Only the first row is correct: Inspect a few later source values. If their lengths or delimiter patterns vary, a fixed-position formula may not apply to all rows. Use a delimiter-based formula or create separate logic for the different patterns. Also confirm that the formula references the intended source column after filling down.
Excel rejects a formula with commas: Some regional settings use semicolons as argument separators. If Excel flags a formula’s punctuation, replace the commas between arguments with semicolons, while keeping commas inside quoted text unchanged. Function names may also be localized in some language editions.
Frequently Asked Questions
Can I extract text from the middle of a cell?
Yes. Use MID when the starting character and length are known, or combine delimiter-based functions to find the boundaries. If the text layout varies, use a consistent separator rather than relying on fixed character positions.
How do I extract everything after a space or hyphen?
In Microsoft 365 or Excel 2024, use TEXTAFTER and specify the delimiter in quotation marks, such as a space or hyphen. In older versions, FIND can locate the delimiter and MID can return the characters after it.
Can Excel extract text before the second delimiter?
Yes. In Microsoft 365 or Excel 2024, TEXTBEFORE supports an instance argument for targeting a later delimiter. Alternatively, use FIND with a starting position to locate a second delimiter in older versions. Check whether you want the content before that delimiter or the section between two delimiters.
Will the extracted text update when the original cell changes?
Yes. A formula-linked result recalculates when its source cell changes. To keep a fixed result instead, copy the formula output and use Home > Paste > Paste Values.
Can I extract text from many rows at once?
Yes. Enter and verify the formula in the first row, then fill it down using the fill handle or copy and paste. Relative references adjust for each row. Review a few results when your source data has inconsistent formats.
Related
- Forecast Sheet in Excel 2007: What You Can Do Instead
- How to Enable Merge and Center in Excel: Quick Fixes
- Forecast Sheet in Excel: Enable It and Build a Forecast
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IF Statement for Dates: Compare DatesIf A2 contains a due date and B2 contains the date you want to check, enter =IF(A2<B2,"Overdue","Not overdue")…
- Excel SUMIF Criteria Examples for Text, Dates, and NumbersExcel’s SUMIF function adds values when one matching condition is true. With categories in A2:A7 and amounts in…
- Excel Min Date: Earliest Date and MIN FormulaIn Excel for Microsoft 365 on Windows, the earliest date Excel can store as a date in its…
- Excel LEN Function Examples for Text, Spaces, and DataExcel’s LEN function returns the number of characters in a value, including spaces. For example, if A2 contains…
- Excel Timesheet Formulas for Overtime, Breaks, and PayFor a basic Excel timesheet, put a shift’s start time in B2, end time in C2, and unpaid…
- Rank in Excel Ignoring Blanks: Formula and ExamplesYes. In Microsoft Excel, RANK.EQ ignores blank cells in its reference range automatically. To also leave the rank…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.