Extract Part of Text in Excel: Formulas and Steps

Formulas & functions · Updated November 18, 2026

To extract part of text in Excel, use LEFT, RIGHT, or MID for characters in known positions, and TEXTBEFORE or TEXTAFTER for text around a delimiter such as a hyphen or @ sign. Put the formula in a new column, check the result, and fill it down. These steps are written for Microsoft Excel for Microsoft 365 on Windows; the same formulas work in Excel for Mac, and most also work in Excel for the web.

Before starting, identify whether the part you need is defined by its position or by a character that separates it from the rest. The examples use a cell reference of A2 or B2; replace it with the cell containing your own text.

What You’ll Need

  • An Excel workbook with the text you want to extract.
  • A blank column or range for the extracted results, so you keep the original text intact.
  • A clear rule for the part you want: a number of characters, a starting position, or a delimiter such as a comma, space, hyphen, or @ sign.

Step-by-Step

  1. Choose a suitable example and inspect the text. Click the first cell containing a value to work with, such as A2, and note whether all entries follow the same pattern. For example, product codes might look like INV-2026-047, while email addresses have a consistent @ delimiter but different-length names and domains. If you are extracting a fixed number of characters, count from the first character, not from zero. If you are using a delimiter, check whether it appears once or multiple times and decide which occurrence matters.
  2. Extract characters from the beginning with LEFT. Use this when the desired part always starts at the first character and has a fixed length. Assume A2 contains INV-2026-047 and you want the three-letter prefix. Enter =LEFT(A2,3) in a blank cell. The result is INV, because Excel returns the first three characters of A2. Change 3 to the number of characters you need. For instance, if every code begins with a two-character region marker, use 2 instead.
  3. Extract characters from the end with RIGHT. This is useful for fixed-length suffixes, such as the final digits of a reference code. Assume A2 contains INV-2026-047 and the last three characters are the part you need. Enter =RIGHT(A2,3). The result is 047. Excel treats the extracted characters as text, so the leading zero is retained. That is helpful for identifiers, which should not be treated as arithmetic values.
  4. Extract from a known position with MID. Use this when the part begins after a known number of characters and has a known length. In INV-2026-047, the year begins at character 5 and occupies four characters. Assume that code is in A2; enter =MID(A2,5,4). The result is 2026. MID counts the first character as position 1, including punctuation and spaces. If the format changes, such as a prefix becoming longer, a fixed starting position may no longer identify the right part.
  5. Extract text before or after a delimiter. For data with variable-length sections, a delimiter-based formula is usually more reliable than counting characters. Assume B2 contains alex.chen@example.com. To return the text before the @ sign, enter =TEXTBEFORE(B2,"@"); the result is alex.chen. To return what follows the @ sign, enter =TEXTAFTER(B2,"@"); the result is example.com. TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024. They are not available in every older Excel version.
  6. Use FIND and MID when you need text between separators. This option works in older Excel versions as well as current ones, and finds the positions of delimiters rather than relying on a fixed character count. Assume A2 contains INV-2026-047, with the year between the first and second hyphens. Enter =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1). The result is 2026: the first hyphen is at position 4, so extraction starts at position 5, and the next hyphen is at position 9, leaving four characters between them. This formula assumes both hyphens are present and that the year is the text between the first two.
  7. Fill the formula down and review the results. Press Enter after entering a formula, then select that result cell. Drag the small square at its lower-right corner down the column, or double-click the square to fill alongside adjacent data. Excel adjusts references such as A2 to A3 as it copies the formula. Check several rows, especially the first and last, and look for blank cells, unexpected separators, or errors before using the extracted column elsewhere.
  8. Choose whether you need formulas or fixed values. Formula results update when the original text changes. If you need a static result, copy the completed output range, then choose Home > Paste > Paste Values. Keep the original column until you have confirmed the pasted values are correct. If you only need to split a column once, you can also use Data > Text to Columns: select the source cells, choose Delimited, select the separator, review the preview, and choose a destination that will not overwrite other data. Text to Columns writes separate values into adjacent columns rather than creating a live formula.
  9. Consider Flash Fill for a quick one-time pattern. In desktop Excel, type the result you want for the first row in the next column, then begin the second row if needed so Excel can recognize the pattern. Select the result column and choose Data > Flash Fill, or press Ctrl+E on Windows. Review the filled values because Flash Fill infers a pattern rather than calculating a rule. On a Mac, use Data > Flash Fill if available in your Excel version; in Excel for the web, use a formula instead if Flash Fill is not offered.

Common Mistakes

Counting positions from zero: LEFT, RIGHT, and MID count characters from 1. In the code INV-2026-047, the first hyphen is character 4, not character 3. Count punctuation and spaces too.

Using a fixed position for inconsistent data: MID works well when values follow one format. If prefixes have different lengths, a formula that starts at character 5 may return part of the prefix for some rows. Use a delimiter-based approach where possible, and inspect unusual records separately.

Choosing the wrong delimiter occurrence: A text value may contain several hyphens or spaces. Decide whether you want the text before the first separator, after the last one, or between two separators. A formula that uses the first occurrence can return a different result from one designed for the last.

Overwriting source data: Text to Columns can place results in cells to the right of the selected column. If those cells contain information, Excel may replace it. Insert blank columns or choose an empty destination before completing the operation.

Assuming an extracted number is a number: A result such as 047 is text when it comes from a text-extraction formula. That preserves the leading zero, but it may not behave like a numeric value in calculations. Decide whether the value is an identifier or a quantity before converting it.

Tips

  • Keep the source text and extracted result in separate columns while building and checking your method. This makes errors easier to spot and gives you a way to revise the extraction.
  • Use a short test range first if the source contains many formats. Include a normal example, the shortest value, the longest value, and any row with extra punctuation or spaces.
  • For a one-character delimiter, make sure you enter the exact character used in the data. A regular hyphen, an en dash, and an em dash look similar but are different characters.
  • Use the formula bar to inspect the full source value if the cell display is shortened by column width. Widening the column can also make spaces or punctuation easier to see.
  • If the data contains leading or trailing spaces, clean or account for them before extracting. Spaces count as characters, so they can change a fixed-position result.
  • When sending a workbook to someone with an older Excel release, test formulas that use newer functions first. For broad compatibility, use LEFT, RIGHT, MID, and FIND when their logic suits the data.

Troubleshooting

The formula shows #NAME? Your Excel version may not recognize TEXTBEFORE or TEXTAFTER. They require Microsoft 365 or Excel 2024. If those functions are unavailable, use FIND with LEFT or MID where the delimiter pattern is consistent, or use Text to Columns for a one-time split.

The formula shows #VALUE! or returns an error for some rows. A searched-for delimiter may be missing from one or more cells, or a fixed-length formula may ask for more characters than the value contains. Check the failing source cell and compare its punctuation and length with a working row. If the source formats vary, handle the exceptional rows separately rather than assuming every record follows the same rule.

The result includes part of the delimiter or misses a character. Review the start and length arguments. For extraction after a delimiter, the starting position normally needs to be one character after the delimiter. For text between two delimiters, the requested length is the distance between their positions minus one. Verify those positions on a sample value before filling the formula down.

The formula appears as text instead of calculating. Check whether the cell is formatted as Text, or whether an apostrophe was entered before the equals sign. Change the cell format to General, then re-enter the formula. If Excel is displaying formulas throughout the sheet, toggle Show Formulas from the Formulas tab or press Ctrl+` on Windows; the key is usually above Tab.

Some rows look correct but others do not. Sort or filter the source column to find values that use a different separator, include extra spaces, or omit a section. A single extraction rule cannot reliably interpret multiple formats unless you explicitly account for those variations.

Frequently Asked Questions

Can I extract text between two characters in Excel?

Yes. If the delimiters are consistent, use MID together with FIND to locate the starting and ending characters, as in the hyphen example above. For more complex or inconsistent text, split the value into columns or handle exceptions separately.

How do I extract text after the second delimiter?

First decide which delimiter and occurrence mark the boundary. TEXTAFTER supports an occurrence number in Microsoft 365 and Excel 2024, so you can specify the second occurrence. If you use an older version, locate the second delimiter with nested FIND functions, then extract from the following character.

How do I extract the first word from a cell?

If words are separated by a regular space, use TEXTBEFORE with a space delimiter in Microsoft 365 or Excel 2024. Check for leading spaces or cells containing only one word, since those cases can affect the result or require separate handling.

Can I extract text without a formula?

Yes. Use Data > Text to Columns when the separator is consistent and you want to split a column into neighboring columns. Flash Fill can infer a pattern from examples in desktop Excel, but review its results carefully because they are not driven by a formula.

Will the extracted text update when I edit the original cell?

Yes, if the result is a formula that refers to the original cell. It will recalculate when the source changes. If you paste the result as a value, it becomes static and will not update automatically.

Why does my extracted result lose a leading zero?

Excel may interpret the result as a number in some workflows, and numeric values do not retain leading zeros. Keep identifiers as text, or use a text-extraction formula that returns text. Verify the result after using Text to Columns or converting data types.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.