Excel Function to Extract Part of a String: Examples

Formulas & functions · Updated November 18, 2026

Excel has several functions for extracting part of a string: use LEFT for characters at the start, RIGHT for characters at the end, and MID for characters at a specified position. If the text is separated by a known delimiter, such as a hyphen or @ sign, TEXTBEFORE or TEXTAFTER can often extract it more directly in Microsoft 365.

The main Excel functions for extracting text

The best formula depends on what you know about the text. A fixed character count is a good fit for LEFT or RIGHT; a known position calls for MID; and a separator between fields points to TEXTBEFORE, TEXTAFTER, or TEXTSPLIT. These functions return text, even when the extracted characters look like a number. That distinction matters if you plan to calculate with the result later.

  • LEFT returns a specified number of characters from the beginning of a string.
  • RIGHT returns a specified number of characters from the end.
  • MID returns a specified number of characters beginning at a position you choose.
  • TEXTBEFORE and TEXTAFTER return the text on one side of a delimiter.
  • TEXTSPLIT divides text around a delimiter and places the pieces in separate cells.

For example, an invoice ID such as INV-2026-084 has a predictable structure. If it is in A2, the first three characters are the prefix, the four characters beginning at position five are the year, and the final three characters are the sequence number. The next sections show how to build each type of formula and when to prefer one approach.

Extract a fixed number of characters with LEFT, RIGHT, and MID

Use LEFT and RIGHT when the number of characters to return is consistent across the data. Their syntax is LEFT(text, [num_chars]) and RIGHT(text, [num_chars]). The character-count argument is optional and defaults to 1. MID uses MID(text, start_num, num_chars); its starting position is counted from 1, not 0. Spaces and punctuation count as characters.

With invoice IDs in A2:A20 and the example value INV-2026-084 in A2, this returns INV: =LEFT(A2,3). With the same cell layout, this returns 084: =RIGHT(A2,3). To return the four characters beginning at position 5 from A2, use =MID(A2,5,4); the result is 2026. The hyphens occupy positions 4 and 9, which is why the year starts at position 5.

These formulas are simple and efficient when the text format is stable. They become fragile if a prefix or suffix changes length. For example, RIGHT(A2,3) still returns three characters if some IDs end in a two-character code or a four-character code; it cannot infer where the meaningful field ends. In that situation, extract relative to a delimiter instead.

Extract text around a delimiter

A delimiter is a character or sequence that separates parts of a string. Common examples include a hyphen, comma, space, slash, colon, or the @ sign in an email address. TEXTBEFORE and TEXTAFTER let you specify the delimiter rather than count characters manually. They are available in Microsoft 365 and Excel 2024; they are not available in every older Excel release.

With email addresses in A2:A20 and mira@example.com in A2, this returns the name before the @ sign: =TEXTBEFORE(A2,"@"). With the same layout, this returns the domain after it: =TEXTAFTER(A2,"@"). Both results change appropriately if the length of the name or domain changes, provided the delimiter remains present.

For the invoice ID in A2, this extracts the field between the first and second hyphens: =TEXTBEFORE(TEXTAFTER(A2,"-"),"-"). The result is 2026. This nested formula first returns everything after the first hyphen, then returns everything before the next one. It is useful when the field length can vary but the separators are reliable.

TEXTBEFORE and TEXTAFTER also accept optional arguments for selecting a delimiter occurrence, matching case, and specifying what to return if the delimiter is missing. When you only need the first occurrence and the data is consistent, the basic formula is easier to read. Remember that a delimiter must match exactly: a space before a pipe character is different from a pipe character alone.

Use FIND or SEARCH when the position must be calculated

FIND and SEARCH locate the position of one piece of text inside another. FIND is case-sensitive; SEARCH is not. Both return a number that can be used as an input to MID, LEFT, or RIGHT. They are especially useful in older Excel versions that do not include TEXTBEFORE and TEXTAFTER, or when you need to locate a delimiter before applying a more customized extraction.

With product descriptions in A2:A20 and the value Item: Notebook in A2, this returns the position of the colon: =FIND(":",A2). The result is 5. To return the text after that colon and its following space, use =MID(A2,FIND(":",A2)+2,LEN(A2)). With that same A2 value, the result is Notebook. LEN returns the total number of characters; asking MID for that many characters is sufficient to reach the end of the string.

To extract text inside parentheses, the start and end positions can both be calculated. With descriptions in A2:A20 and Order (priority) in A2, use =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1). The result is priority. This formula assumes that each description contains one opening and one closing parenthesis in the expected order. If the characters are missing, FIND returns an error, so the formula needs an error-handling plan for inconsistent data.

Split a string into multiple cells with TEXTSPLIT

If the goal is to separate every field rather than return just one part, TEXTSPLIT can be more convenient than writing several extraction formulas. It is available in Microsoft 365 and Excel 2024. It splits text using a column delimiter, a row delimiter, or both, and returns the results into neighboring cells when there is room for them to spill.

With comma-separated values in A2:A20 and red,green,blue in A2, use =TEXTSPLIT(A2,","). The result spills horizontally into three cells: red, green, and blue. If the source uses semicolons, put a semicolon inside the delimiter argument instead. For line breaks or more complex lists, TEXTSPLIT has additional arguments, but the simplest formula is usually best when there is one consistent separator.

Spill formulas need empty cells in the destination area. If a neighboring cell already contains data, Excel may show a #SPILL! error rather than overwrite it. Also inspect the source for spaces: splitting on a comma does not automatically remove a space after the comma. If the pieces have unwanted leading or trailing spaces, TRIM can help clean them; be aware that TRIM also reduces repeated internal spaces to single spaces.

How to choose the right extraction method

Start by identifying the rule that defines the part you want. A formula based on the structure of the data is easier to maintain than one based on a character count that only happens to work for the current sample. These questions help narrow down the method:

  • Is the part always the same number of characters from an edge? Choose LEFT or RIGHT.
  • Does it start at a known character position? Choose MID.
  • Is it bounded by a consistent separator? Choose TEXTBEFORE or TEXTAFTER in a supported version, or combine FIND or SEARCH with MID in an older version.
  • Do you need all the separated pieces in adjacent cells? Choose TEXTSPLIT when available.
  • Does capitalization matter when locating a marker? Use FIND for a case-sensitive match or SEARCH for a case-insensitive one.

Consider a code that begins with two letters, followed by a variable-length number, then a slash and a region code. LEFT might reliably return the two-letter prefix, but a fixed MID formula may not reliably return the number if its length varies. If the number is always between the prefix and slash, a delimiter-based method is more adaptable. Before filling a formula down a large column, test it against short and long values, missing delimiters, and any exceptional formats in the source data.

Common problems and ways to avoid them

Extraction formulas are only as reliable as the assumptions behind them. A formula can be syntactically correct and still return the wrong part if the source has inconsistent spacing, unexpected punctuation, or a different number of fields. Check a representative sample before relying on the result for reporting or another calculation.

  • Wrong character count: MID positions start at 1, and spaces and punctuation count. Use a short sample and write out the positions when a result seems shifted.
  • Delimiter not found: FIND and SEARCH return #VALUE! when the search text is absent. TEXTBEFORE and TEXTAFTER also need a missing-delimiter strategy if some rows do not contain the separator.
  • Extra spaces: A delimiter such as comma-space is not the same as a comma. Match the actual source or clean the text first.
  • Unexpected capitalization: FIND distinguishes uppercase and lowercase; SEARCH does not.
  • Numbers returned as text: A piece extracted from an ID remains text, even if it contains only digits. If you need arithmetic, convert it deliberately, for example with VALUE, and check that every extracted value is numeric.
  • Spill area blocked: TEXTSPLIT needs empty destination cells for its output.

IFERROR can provide a fallback when a formula fails, but it should not conceal a data-quality issue that needs attention. With email addresses in A2:A20, this returns Not found if A2 does not contain an @ sign: =IFERROR(TEXTBEFORE(A2,"@"),"Not found"). The fallback is useful for a report, but it is still worth finding out why a supposedly valid address lacks the expected delimiter.

Version and compatibility notes

LEFT, RIGHT, MID, FIND, SEARCH, and LEN are established Excel functions and are available in older desktop editions as well as Microsoft 365. TEXTBEFORE, TEXTAFTER, and TEXTSPLIT require Microsoft 365 or Excel 2024. If a workbook will be opened in an older version, a formula using one of these newer functions may not calculate there; use a compatible alternative or confirm the recipients’ Excel versions first.

The formulas in this article use commas between arguments, as in standard English-language Excel settings. Some regional settings use semicolons instead, so Excel may require that separator when you enter a formula. In current Microsoft 365 for Windows, Mac, and Excel for the web, the function names and basic extraction behavior are generally the same, though release availability can differ. If Excel does not recognize a newer function, check the installed version and update status before assuming the spelling is wrong.

Frequently Asked Questions

What is the simplest Excel formula to extract part of a text string?
Use LEFT, RIGHT, or MID depending on where the characters are. For example, LEFT returns characters from the beginning, while MID starts at a position you specify. If a separator defines the part, TEXTBEFORE or TEXTAFTER may be simpler.

How do I extract text after a specific character?
In Microsoft 365 or Excel 2024, use TEXTAFTER with the cell and delimiter, such as =TEXTAFTER(A2,"-") for text after a hyphen. In an older version, combine MID with FIND and LEN, and account for rows where the hyphen may be missing.

How do I extract text between two characters?
Use TEXTBEFORE and TEXTAFTER together when the markers appear in a consistent order, or use FIND to calculate the start and length for MID. Test the formula with missing or repeated markers, since those cases can change which occurrence is found or cause an error.

Can Excel extract a word from the middle of a sentence?
Yes. If the word has a predictable character position and length, MID can return it. If it is separated by spaces or punctuation, locate those delimiters with FIND or use TEXTSPLIT to divide the sentence into fields, then select the relevant piece.

Why does my extraction formula return #VALUE!?
A common cause is a FIND or SEARCH formula looking for text that is not present. A negative character count supplied to MID can also produce an error. Check the source value, the exact delimiter, and the calculated start and length before adding an error fallback.

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.