Excel Formula for Extracting Text from a Cell

Formulas & functions · Updated November 18, 2026

To extract a set number of characters in Excel, use =LEFT(A2,3) when the text is in A2 and you need its first three characters; for example, it returns INV from INV-2026-0478. To extract text based on a separator such as a space or hyphen, use TEXTBEFORE or TEXTAFTER in Microsoft 365 or Excel 2024.

Choose a formula based on the text pattern

The right formula depends on what you know about the value in the cell. If the text to keep is always at the beginning or end, use LEFT or RIGHT. If it occupies a known position in the middle, use MID. If a delimiter marks where the desired part begins or ends, a delimiter-based formula is usually easier to maintain.

For example, a product code might always have a three-letter prefix, while a full name might contain a variable number of characters before a comma. The first pattern is about position; the second is about a boundary. Choosing the formula to match the pattern matters because a formula that counts characters can break when a name or description changes length.

  • LEFT: take characters from the start of a cell.
  • RIGHT: take characters from the end.
  • MID: take characters beginning at a specified position.
  • TEXTBEFORE and TEXTAFTER: return the text on one side of a delimiter.

Extract a fixed number of characters

LEFT and RIGHT are useful when the part you need has a fixed length. Suppose A2 contains INV-2026-0478. With that value in A2, =LEFT(A2,3) returns INV. The number 3 tells Excel how many characters to take, counting from the left.

With the same value in A2, =RIGHT(A2,4) returns 0478. This is often useful for account suffixes, product identifiers, or codes with a standard-length ending. Excel returns extracted characters as text. That is helpful when leading zeros matter: the result 0478 remains text rather than becoming the number 478.

Use MID when the section starts at a known position inside the cell. With INV-2026-0478 in A2, =MID(A2,5,4) returns 2026. The second argument is the starting character position, counting from 1; the third is the number of characters to return. In this example, character 5 is the first digit of the year.

These formulas do not require the cell to contain exactly the expected number of characters. If a value is shorter than the requested count, Excel returns the characters that are available. However, a fixed-position formula can still return the wrong part if the format changes—for example, if a prefix becomes four characters instead of three.

Split text at a delimiter

A delimiter is a character or short string that separates pieces of text. Common examples include a comma, hyphen, slash, space, colon, or a combination such as space-pipe-space. When the part to extract can vary in length, the delimiter provides a more dependable boundary than counting characters.

In Microsoft 365 or Excel 2024, TEXTBEFORE returns the text before a delimiter. If B2 contains Maya Chen | Sales, then =TEXTBEFORE(B2," | ") returns Maya Chen. The delimiter includes a space on both sides of the vertical bar, so Excel removes that separator rather than leaving an extra space in the result.

With the same value in B2, =TEXTAFTER(B2," | ") returns Sales. These functions are especially convenient for lists where the text on either side of the delimiter has different lengths. They can also work with delimiters such as @ in an email address or a comma between a surname and given name.

TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024, but not in every older Excel release. If a workbook will be opened by people using older versions, consider the compatible alternatives below or check which Excel versions your audience uses.

Use older formulas when TEXTBEFORE is unavailable

In older Excel versions, combine LEFT or MID with FIND. Suppose B2 contains Maya Chen | Sales. With that layout, =LEFT(B2,FIND(" | ",B2)-1) returns Maya Chen. FIND locates the first character of the separator, and subtracting 1 makes LEFT stop just before it.

To return the text after the same separator, with Maya Chen | Sales in B2, use =MID(B2,FIND(" | ",B2)+LEN(" | "),LEN(B2)). This returns Sales. LEN counts the separator’s characters so the starting point skips the entire delimiter; the final LEN(B2) is a sufficiently large character count for the remaining text.

FIND distinguishes uppercase and lowercase letters, while SEARCH does not. That distinction generally does not matter for punctuation separators, but it can matter when locating a word. These older formulas are more verbose than TEXTBEFORE and TEXTAFTER, yet they are useful when compatibility with older workbooks is important.

Extract text between markers or find a word

Sometimes the desired text is enclosed by two markers, such as parentheses. If A2 contains Order (urgent), use =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1) to return urgent. The first FIND locates the opening parenthesis; the second locates the closing one. The formula starts after the opening marker and calculates how many characters lie between them.

This approach assumes the cell has both markers and that the first opening parenthesis pairs with the first closing parenthesis. If the text may contain several pairs, nested parentheses, or missing markers, the formula needs more careful handling. For complex or irregular text, a simple one-off extraction formula may be less suitable than cleaning the source data or using a data-import tool.

When searching for a word rather than a punctuation mark, choose the search function deliberately. For example, if A2 contains Item: Blue pen, =SEARCH("pen",A2) returns 11, the position where the match begins. With the same cell content, =FIND("Pen",A2) returns an error because FIND is case-sensitive and the cell contains lowercase pen. Search functions return a position; combine that position with MID, LEFT, or RIGHT when you want the matching text itself.

Handle spaces, repeated delimiters, and missing matches

Extra spaces are a common reason an extracted result looks wrong. A value may contain spaces before or after the meaningful text, or around a separator. If you only need to remove leading and trailing ordinary spaces from the extracted result, wrap the extraction in TRIM. For instance, if B2 contains Maya Chen | Sales, =TRIM(TEXTAFTER(B2,"|")) returns Sales. The formula extracts after the bar, then trims the spaces at the edges.

TRIM does not remove every kind of whitespace or clean all invisible characters imported from websites and other systems. It also reduces repeated ordinary spaces between words to one space. If internal spacing is meaningful, avoid applying it automatically. When a result looks identical to its source but fails to match another value, hidden characters or nonbreaking spaces may be the cause.

If the delimiter occurs more than once, the default behavior of TEXTBEFORE and TEXTAFTER uses its first occurrence. Their optional instance argument can target another occurrence. For example, with North-East-Central in A2, =TEXTAFTER(A2,"-",2) returns Central, the text after the second hyphen. This can be useful for structured codes, but confirm that the delimiter count is consistent in the source data.

A missing delimiter can cause a formula to return an error, especially when using FIND. The optional if_not_found argument in TEXTBEFORE and TEXTAFTER can provide a fallback; alternatively, IFERROR can handle errors from older formulas. Choose a fallback that makes missing data visible when appropriate. Returning an empty string may look tidy, but it can conceal that a row did not follow the expected pattern.

Choose formulas, Text to Columns, or Power Query

A formula is a good fit when you want an extracted result to update automatically as the source cell changes. Enter the formula in a neighboring column and fill it down. On Windows, drag or double-click the fill handle at the lower-right corner of the selected cell; Excel tables can also fill a calculated column automatically. On Mac, the same fill handle is available, though trackpad and mouse gestures may differ.

For a one-time split into adjacent columns, Excel’s Text to Columns tool may be simpler. Select the source column, then choose Data > Text to Columns on the Windows ribbon. Follow the wizard to specify a delimiter or fixed-width breaks. This changes the selected data into separate columns rather than creating formulas that stay linked to the original text, so make a copy first if the original values need to be preserved.

For repeatable imports and larger cleaning tasks, Power Query can split a column by a delimiter and refresh the transformation when the source data changes. It is useful when extraction is part of a recurring data-preparation workflow. A simple cell formula is usually easier for a small, live worksheet; the best choice depends on whether you need a dynamic cell result, a one-time split, or a reusable import process.

Troubleshoot formula results

When a formula returns an unexpected result, check the source pattern before rewriting the formula. A delimiter that appears more than once, a slightly different separator, or an extra space can change where the extraction stops. For example, a hyphen-minus (-) is not the same character as an en dash (–), even though they can look similar in a cell.

  • Confirm that the formula references the intended cell and uses the correct delimiter, including spaces.
  • Check whether the source contains the delimiter in every row. A missing one can cause an error or an unexpected fallback.
  • Verify that a fixed character count still matches the current code or label format.
  • Check whether a result is text or a number. Keeping an identifier as text is often necessary to preserve leading zeros.
  • If a formula appears as text instead of calculating, check whether the cell is formatted as Text or the entry begins with an apostrophe. Change the cell format if needed, then re-enter the formula.

Excel’s formula argument separator can depend on regional settings. The examples here use commas, as in =LEFT(A2,3). If Excel expects semicolons on your computer, use the separator shown by your installation when entering formulas. The function names and extraction logic remain the same.

Frequently Asked Questions

What is the simplest Excel formula to extract text?
If you need a fixed number of characters from the beginning, use LEFT; for characters from the end, use RIGHT. For text separated by a delimiter in Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER are often the simplest choices.

How do I extract text between two characters in Excel?
For a simple pair of markers, locate both with FIND and use MID to return the characters between them. The method assumes both markers are present and that their order is consistent; irregular or nested markers require a more robust approach.

Can Excel extract text before a space or hyphen?
Yes. In Microsoft 365 or Excel 2024, use TEXTBEFORE with the space or hyphen as the delimiter. In older versions, combine LEFT with FIND. Include any spaces that are part of the separator so the result does not retain an unwanted blank.

Why does my text extraction formula return an error?
A common cause is that the formula cannot find the delimiter it expects. Check for missing separators, different dash or quote characters, and extra spaces. You can provide a fallback with the optional not-found argument in newer text functions or handle an error with IFERROR.

Will the formula keep leading zeros in extracted codes?
Text extraction functions return text, so a result such as 0478 retains its leading zero. Avoid converting the result to a number if those zeros are part of an identifier rather than a numeric quantity.

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.