Google Sheets: How to Extract Text From a String

Yes. In Google Sheets, extract text from a string with functions such as LEFT, RIGHT, MID, SPLIT, or REGEXEXTRACT. For example, if A2 contains ORD-8472-CA, =REGEXEXTRACT(A2,"[0-9]+") returns 8472. Choose a function based on whether you know the character positions, have a delimiter, or need to match a pattern.

Start with the shape of the text

Text extraction means returning only a part of a cell’s contents while leaving the original value unchanged. A cell might contain a product code, a full name, an email address, or a sentence; the goal could be to pull out its prefix, a word, a number, or the text between two markers. The best formula depends on how consistently that information is arranged.

Before writing a formula, inspect a few real examples and decide which rule describes the part you need:

Fixed position or length: the desired characters always begin or end in the same place.
A delimiter: a hyphen, comma, space, colon, or other character marks the boundary.
A recognizable pattern: the target is defined by a rule, such as consecutive digits or an email-like address.

For example, the account code AB-4921-NY has hyphens as delimiters, while a description such as Case 4921 shipped may be easier to handle by matching its digits. If formatting varies from row to row, a pattern-based formula is often more reliable than counting characters.

Extract by character position with LEFT, RIGHT, and MID

Use these functions when the target’s position is known. With the text INV-2026-071 in A2, LEFT returns characters from the beginning, RIGHT returns them from the end, and MID returns a section starting at a specified character position. Character positions start at 1.

With the text in A2, =LEFT(A2,3) returns INV.
With the text in A2, =RIGHT(A2,3) returns 071.
With the text in A2, =MID(A2,5,4) returns 2026.

The three arguments in MID are the source text, starting position, and number of characters. These formulas are straightforward for fixed-width identifiers, but fragile if someone inserts an extra character. If the number of characters before the target changes, use a delimiter or pattern instead.

Find text around a delimiter

When a separator identifies the boundary, combine FIND with LEFT, MID, or RIGHT. Assume A2 contains West:Seattle. This formula returns everything before the colon: =LEFT(A2,FIND(":",A2)-1). FIND returns the colon’s position, and subtracting 1 excludes the colon from the result.

To return everything after the first colon, use =MID(A2,FIND(":",A2)+1,LEN(A2)). Here, LEN supplies a length long enough to capture the remainder of the cell. The result is Seattle. If the value is instead West:Greater Seattle, the same formula returns the full text after the colon, including its space.

FIND is case-sensitive. Use SEARCH when the delimiter or search text may appear in different letter cases; for punctuation, the distinction usually does not matter. Both functions return an error if they cannot find the target. To display a blank in that situation, wrap the extraction in IFERROR. With a colon-separated value in A2, for example: =IFERROR(MID(A2,FIND(":",A2)+1,LEN(A2)),"").

Split a cell into separate pieces

If you want to divide a whole value at a separator rather than return just one portion, use SPLIT. With North|Hardware|Active in A2, =SPLIT(A2,"|") places the three pieces into neighboring cells across the row: North, Hardware, and Active. Make sure the cells to the right are empty so the results have room to expand.

To retrieve just one resulting piece, use INDEX around the split result. With the same value in A2, =INDEX(SPLIT(A2,"|"),1,2) returns the second piece, Hardware. The row argument is 1 because the split results are arranged horizontally; the column argument is 2 because the desired item is second.

By default, SPLIT can treat each character in its delimiter argument as a separate delimiter. If the separator is a multi-character sequence that must be treated as one unit, specify the optional arguments. For instance, with A2 containing red--blue, =SPLIT(A2,"--",FALSE,FALSE) uses the full double hyphen as the separator and keeps empty pieces if they occur. Review the arguments carefully when repeated delimiters or blank fields matter.

Match a pattern with REGEXEXTRACT

Use REGEXEXTRACT when the target can be described by a regular expression rather than a fixed position. It returns the first part of the source text that matches the pattern. With Order 8472 ready in A2, =REGEXEXTRACT(A2,"[0-9]+") returns 8472. In the pattern, [0-9] means a digit and + means one or more of the preceding item.

For example, a simple pattern for a sequence of letters is [A-Za-z]+. With SKU-Blue-27 in A2, =REGEXEXTRACT(A2,"[A-Za-z]+") returns the first matching sequence, SKU. To match the letters after the first hyphen instead, the pattern needs to account for that boundary, or you can split on the hyphen and select the relevant piece.

Patterns are useful for values that contain the same kind of information in varying positions, but they need to match the actual data. A pattern such as [0-9]+ extracts the first run of digits; it does not know whether those digits are an order number, a year, or a postal code. If a cell has multiple digit groups, make the surrounding pattern more specific and check the result against representative rows.

Google Sheets regular expressions use RE2 syntax. Some features available in other regex tools, including lookaround assertions, are not supported. Also, capturing parentheses affect what REGEXEXTRACT returns: capturing groups can be returned as separate results. Start with a simple pattern, then test it on values with missing, repeated, or unexpected characters. If no match exists, the function returns an error; IFERROR can provide a fallback such as a blank cell.

Choose a method that can handle your data

A useful rule is to choose the simplest formula that matches the structure and remains valid as the data changes. Fixed-position functions are easy to read when the text is truly fixed-width. Delimiter formulas adapt when the length of the sections varies but the separator stays dependable. Regular expressions can handle more variation, but a complex pattern may be harder for another person to maintain.

Use LEFT, RIGHT, or MID for fixed character positions.
Use FIND or SEARCH with an extraction function when one boundary character marks the target.
Use SPLIT when you need several delimiter-separated fields at once.
Use REGEXEXTRACT when the target follows a recognizable pattern across variable positions.

Test the chosen formula on ordinary cases and edge cases: a missing delimiter, a delimiter at the beginning or end, extra spaces, and a value containing the separator more than once. These checks help reveal whether the formula’s rule matches the real data rather than just one example.

Common errors and ways to troubleshoot them

If a formula returns an error, first check whether the referenced cell actually contains the expected marker. FIND and SEARCH return an error when their search text is absent, and REGEXEXTRACT does the same when no match is found. Use IFERROR only when a fallback is appropriate; hiding every error can make missing or malformed source data harder to notice.

If the result includes a space, that may be part of the source text rather than a formula mistake. For example, extracting after a colon from City: Seattle begins with a space. You can remove leading and trailing spaces from the extracted result with TRIM. With that value in A2, =TRIM(MID(A2,FIND(":",A2)+1,LEN(A2))) returns Seattle.

Another common issue is using the wrong occurrence of a repeated delimiter. FIND locates the first occurrence of its search text, so a formula built around it may not isolate the final section of a value with several hyphens. In such cases, consider splitting the text and selecting the needed field, or writing a pattern that identifies the intended section. Also check for inconsistent source data: a formula cannot reliably extract a field that is not marked consistently.

Fill the formula down and keep results current

For a list, put the formula in the result column beside the first source value, then fill it down for the other rows. In Google Sheets, select the formula cell and drag its small fill handle down, or copy and paste it into the range. A relative reference such as A2 changes to A3, A4, and so on as the formula moves. If you copy a formula to another location and need its source column to stay fixed, an absolute reference such as $A2 can help.

Formula-based extraction updates when the source cell changes, which is helpful for ongoing records. If you need a static result for a handoff or snapshot, copy the results and use Edit > Paste special > Values only. Keep the original source column if you may need to audit or redo the extraction later. For large sheets, check that formulas reference only the needed ranges and that split results will not overwrite existing data.

How Google Sheets differs from Excel

The basic functions LEFT, RIGHT, MID, FIND, SEARCH, and LEN are familiar in both Google Sheets and Excel. However, formula availability and behavior are not identical across spreadsheet products and versions. Google Sheets has SPLIT and REGEXEXTRACT; Excel users may use functions such as TEXTSPLIT, TEXTBEFORE, or TEXTAFTER in supported versions, and regex-function availability depends on the Excel version.

If you share a sheet with someone who will open it in Excel, do not assume every formula will translate unchanged. Check the destination version’s supported functions and test the workbook after conversion. For work that stays in Google Sheets, use the Sheets formula syntax and its function help rather than copying a formula designed for Excel.

Frequently Asked Questions

How do I extract text before a dash in Google Sheets?
With the text in A2, use =LEFT(A2,FIND("-",A2)-1). It returns the characters before the first dash. If some cells may not contain a dash, add an error-handling rule or check those source values.

How do I extract text after a specific character?
With the value in A2 and a colon as the marker, use =MID(A2,FIND(":",A2)+1,LEN(A2)). Replace the colon with the character you need. The formula returns everything after its first occurrence.

Can Google Sheets extract numbers from text?
Yes. If A2 contains Ref 5832 closed, =REGEXEXTRACT(A2,"[0-9]+") returns 5832. It extracts the first consecutive group of digits, not necessarily every number in the cell.

How can I extract text between two characters?
For a value such as [blue] in A2, =REGEXEXTRACT(A2,"\[(.*?)\]") can return the text inside the brackets. Test the pattern on your data, especially if the markers can appear more than once or be missing.

Why does REGEXEXTRACT return an error?
It returns an error when the pattern does not match the cell. Check the spelling and punctuation in the source, confirm that the regex syntax is supported by Sheets, and consider IFERROR if a blank or other fallback is suitable.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.