Substring in Google Sheets: How to Extract Text Between Two Characters

How to Extract Text Between Two Characters in Google Sheets

Use MID with two FIND calls, wrapped in IFERROR. This returns whatever sits between the first opening bracket and the first closing bracket.

FIND returns the character position of each delimiter. The +1 steps past the opening bracket so it is not included. The subtraction turns the two positions into a length, and the -1 stops the cut before the closing bracket. IFERROR hands back a blank on rows that have no brackets at all.

Swap the two characters for parentheses, pipes, or dashes and the shape stays identical. The sections below cover the variants: a plain substring, text before a character, text after one, repeated delimiters, and a whole column at once.

How to Get a Substring in Google Sheets

Google Sheets has no function called SUBSTRING. Three functions cover every case between them.

MID counts from 1, not from 0. Position 12 is the E in EMEA because the opening bracket sits at position 11. Hard-coded numbers only work when every row has the same shape, which is why the rest of this guide finds the positions with FIND, SEARCH, SPLIT, or a regular expression instead.

How to Extract Text Before a Character in Google Sheets

Take everything up to the delimiter with LEFT and SEARCH. This turns "dustin@example.com" into "dustin".

SEARCH gives the position of the character and the -1 stops the cut just before it. The IFERROR fallback returns the whole cell when the delimiter is missing, which is usually more useful than a blank here.

SPLIT gets there without arithmetic. It cuts the string at every delimiter, and INDEX picks the field you want.

That same pattern is the fast way to pull a name out of an email address and to extract a domain from a URL.

How to Extract Text After a Character in Google Sheets

Use RIGHT with LEN and SEARCH. LEN gives the total length, and subtracting the delimiter position leaves the count of characters that follow it.

Note that this measures from the first occurrence. When the delimiter repeats and you want everything after the last one, a regular expression is shorter than the position math.

The pattern reads as one or more characters that are not a slash, anchored to the end of the string. On a URL it returns the final path segment.

How to Extract a Fixed Number of Characters From a String

Product codes and SKUs usually have fixed-width parts, so the position arguments can be literal numbers. For a SKU like "US-4471-XL", the three pieces come out with LEFT, MID, and RIGHT.

The moment one row breaks the pattern, say a three-letter size like "XXL", every fixed count downstream is wrong. Fixed positions are only safe on data that a system generated. For anything typed by a human, find the delimiter instead of counting to it.

How to Extract Text Between Two Characters With REGEXEXTRACT

REGEXEXTRACT returns the part of the string captured by the parentheses in your pattern. This is the same bracket job as the lead formula, in one function instead of three.

Two details decide whether this works. Square brackets are regex metacharacters, so each one needs a backslash to be read as a literal. And .*? is lazy, meaning it stops at the first closing bracket. The greedy .* runs to the last closing bracket in the cell and swallows everything between the two pairs.

Pipes and dashes are simpler because only the pipe needs escaping.

When the text runs across multiple lines

Google Sheets uses the RE2 regex engine, where a dot does not match a newline. A cell with a line break between the two delimiters returns nothing. Prepend the dotall flag and the same pattern starts matching across lines.

Why Your MID Formula Returns #VALUE!

There are two separate causes and they need different fixes.

The first is a missing delimiter. FIND and SEARCH return #VALUE! when the character is not in the cell, and that error passes straight out through MID. The second is a negative length. If the closing delimiter sits before the opening one, the subtraction produces a negative number and MID rejects it, even though both FIND calls succeeded.

IFERROR covers both. When you want to know which rows failed rather than hide them, test the delimiter explicitly instead.

REGEXEXTRACT fails differently. It returns #N/A when the pattern matches nothing, and #ERROR! when the pattern itself is invalid, usually an unescaped metacharacter. An #ERROR! means fix the pattern. An #N/A means the data did not match.

FIND vs SEARCH in Google Sheets

FIND is case sensitive and accepts no wildcards. SEARCH ignores case and supports two wildcards: ? for exactly one character and * for any run of characters.

That difference decides the formula. Looking for "x" with FIND will not match a row that stores "10X20", and you get #VALUE! on data that clearly contains the delimiter. Use FIND when case must match exactly, for example separating a lowercase tag from an uppercase code. Use SEARCH on anything typed by a person.

How to Extract Text Between the Second and Third Delimiter

FIND always reports the first match, so it cannot reach the third slash in a path. SPLIT cuts at every delimiter at once and INDEX picks the field by number.

Both FALSE arguments matter. SPLIT takes the form SPLIT(text, delimiter, split_by_each, remove_empty_text). The third argument defaults to TRUE, which splits on each individual character in your delimiter string, so a delimiter of " - " would cut at every space and every dash separately. The fourth also defaults to TRUE, which drops empty fields and silently renumbers everything after two delimiters that sit next to each other.

To get the text between the last pair of brackets instead of the first, let a greedy prefix run to the end and capture what follows it.

How to Extract Text Between Characters Down a Whole Column

REGEXEXTRACT expands over a range, so one formula in row 2 fills the column and keeps filling it as rows arrive.

SPLIT does not behave the same way. Inside ARRAYFORMULA it still processes a single row, so a range gives you one split result rather than a column of them. Use MAP and LAMBDA, which call the function once per cell.

The blank guard matters. Without it, MAP evaluates every empty row below your data and fills the column with errors. When you want the fields in separate columns rather than one, splitting text by delimiter covers that layout.

Why MID and FIND Break on the Second Bracket Pair

The position formula works on the sample row and then reality arrives. Consider a title like "Q3 Launch [EMEA] final [v2]". FIND reports the first opening bracket and the first closing bracket, so the formula returns EMEA. That is correct here and wrong the moment the row you care about is the second pair.

Now the source system changes and wraps the region in parentheses on half the rows. The MID version needs a new pair of FIND calls. The regex version needs different escaping, since parentheses are the capture syntax itself. Neither one adapts, and both go red on rows where the delimiter never appears.

That is the real cost of position math. Not the first formula, which takes a minute, but every variant after it.

How to Extract Text Between Characters When the Delimiters Are Not Consistent

With FITS, you say which two characters mark the boundary and what to do when they are missing.

No escaping, no error wrapper, no off-by-one on the +1 and -1. Rows without brackets come back blank instead of red.

Because the instruction is a sentence, you can be specific about which pair you meant.

Swapping brackets for quotes, dashes, or pipes is a word change, not a rewrite.

Which Substring Method Should You Use

The neighbouring jobs follow the same rules: extracting numbers from text, splitting text by delimiter when you want every field at once, and removing text after a character when you want to delete the tail rather than keep it. For the full picture, read the guide to automating Google Sheets tasks you used to need regex for.

Frequently Asked Questions

How do I extract text between two characters in Google Sheets?

Use MID with two FIND calls: =IFERROR(MID(A2, FIND("[", A2) + 1, FIND("]", A2) - FIND("[", A2) - 1), ""). FIND returns the position of each delimiter, the +1 steps past the opening one, and the subtraction gives the length between them. IFERROR keeps rows that have no brackets blank instead of red.

Is there a SUBSTRING function in Google Sheets?

No. Google Sheets has no function named SUBSTRING. Three functions cover the job: LEFT(text, n) takes characters from the start, RIGHT(text, n) takes them from the end, and MID(text, start, length) takes them from a position you name. =MID(A2, 12, 4) returns EMEA from the string Q3 Launch [EMEA] final.

How do I get the text before a character in Google Sheets?

Use LEFT with SEARCH: =IFERROR(LEFT(A2, SEARCH("@", A2) - 1), A2). SEARCH returns the position of the character and subtracting one stops the cut just before it. =INDEX(SPLIT(A2, "@"), 1, 1) returns the same first field and needs no arithmetic.

Why does my MID formula return #VALUE! in Google Sheets?

Two causes. FIND and SEARCH return #VALUE! when the delimiter is not in the cell at all, and that error passes straight through MID. MID also returns #VALUE! when the length argument is negative, which happens when the closing delimiter sits before the opening one. Wrap the formula in IFERROR to hand back a blank instead.

What is the difference between FIND and SEARCH in Google Sheets?

FIND is case sensitive and takes no wildcards. SEARCH ignores case and accepts the wildcards ? for one character and * for any run of characters. Use FIND when the delimiter case must match exactly, and SEARCH when the source data mixes cases.

How do I extract text between the second and third slash in Google Sheets?

Split the string and take the field you want: =INDEX(SPLIT(A2, "/", FALSE, FALSE), 1, 3). The third argument FALSE treats a multi-character delimiter as one unit, and the fourth FALSE keeps empty fields, so field numbers stay stable when two delimiters sit next to each other.

How do I extract text between characters for a whole column at once?

REGEXEXTRACT expands over a range, so =ARRAYFORMULA(IFERROR(REGEXEXTRACT(A2:A, "\[(.*?)\]"), "")) fills the column from one cell. SPLIT does not expand that way and only ever returns one row, so pair it with MAP: =MAP(A2:A, LAMBDA(v, IF(v = "", "", INDEX(SPLIT(v, "/"), 1, 3)))).