You can use the following formulas to extract certain substrings from text in Google Sheets:

**Method 1: Return Substring from Beginning of String**

#return first 4 characters of string in cell A1 =LEFT(A1, 4)

**Method 2: Return Substring from Middle of String**

#return 4 characters of string in cell A1 starting at position 2 =MID(A1, 2, 4)

**Method 3: Return Substring from End of String**

#return last 4 characters of string in cell A1 =RIGHT(A1, 4)

**Method 4: Return Substring Before Certain Text**

#return all text before the string "there" in cell A1 =LEFT(A1, SEARCH("there", A1)-1)

**Method 5: Return Substring After Certain Text**

#return all text after the string "there" in cell A1 =RIGHT(A1, SEARCH("there", A1)-1)

The following examples show how to use each of these methods in practice.

**Method 1: Return Substring from Beginning of String**

The following screenshot shows how to use the **LEFT()** function to return the first three characters from cell A2:

**Method 2: Return Substring from Middle of String**

The following screenshot shows how to use the **MID()** function to return the five characters in the middle of cell A2, starting at position 4:

**Method 3: Return Substring from End of String**

The following screenshot shows how to use the **RIGHT()** function to return the last three characters from cell A2:

**Method 4: ****Return Substring Before Certain Text**

The following screenshot shows how to use the **LEFT()** and **SEARCH()** functions to return all of the text that comes before the string “there” in cell A2:

**Method 5: ****Return Substring After Certain Text**

The following screenshot shows how to use the **RIGHT()** and **SEARCH()** functions to return all of the text that comes after the string “there” in cell A2:

**Additional Resources**

The following tutorials explain how to perform other common operations in Google Sheets:

How to Round to Significant Figures in Google Sheets

How to Select a Random Sample in Google Sheets

How to Combine Columns in Google Sheets

> #return all text after the string “there” in cell A1

> =RIGHT(A1, SEARCH(“there”, A1)-1)

This is not true, because the second parameter to the RIGHT() is NUMBER OF CHARACTERS.

Example five is producing the correct result only by accident/coincidence.

A formula that actually works in general for all the text after “there” is:

MID(A2, SEARCH(“there”,A2)+LEN(“there”),LEN(A2))

or if you want to use RIGHT

RIGHT(A2,1+LEN(A2)-(SEARCH(“there”,A2)+LEN(“there”)))

Your last formula [ =RIGHT(A1, SEARCH(“there”, A1)-1) ] is incorrect. It only works because “hey” and “man” are the same length. Try the same formula with “hithereman” and you just get “an”. Instead you need to make it a formula of the location and length like this:

=RIGHT(A2, LEN(A2)-SEARCH(“there”, A2)+1-LEN(“there”))

The RIGHT function use shown here is incorrect. You need the length as well as starting position, “-1” is incorrect.

Return Substring After Certain Text – Does not work!!

=MID(A2,SEARCH(” “,A2)+1,99) – This will work

Your Formula for Method 5 is incorrect – it only works here because the length of “man” and “hey” are the same. Try it with a string like “heywhatsupthereman”.

The correct formula should be:

=RIGHT(A1, LEN(A1) – SEARCH(“there”, A1)-1)

Hey Zach – I was trying to do the exact same thing as you picked out in Method 5 – return a substring after some text. Unfortunately it didn’t work:

RIGHT takes the following Syntax: RIGHT (Value, numchar)

So in Method 5 your Search returns 3 (the place from the left that “there” is found). This HAPPENS to work because hey and man are the exact same length

BUT if you use heytherefolk you get olk returned

The correct formula should be:

=RIGHT(A2, (LEN(A2)-SEARCH(“-“,A2)))

@Jeremy : You are completely RIGHT. I was trying the same thing and discovered the same problem with the posted solution. Yours works correctly.

This does not work. For your test string, try “thereman”, or “xheythereman”