How to extract a word in Excel using a formula?

Jason Chan
September 10, 2022

If you have a list where you are trying to extract a certain part of the string in a programmatic way, you’ve come to the right place. One of the most powerful things you can do in a spreadsheet is manipulate data and extract certain things from a cell. If the data is consistent, you can often apply rules using spreadsheet functions to get what you need.

Here are some of the most common combinations to find text or a string in Excel or Google Sheets:

You can also click here to get the formulas with examples to use right away via copy and paste.

The functions that will help you are the following: LEFT, RIGHT, MID, FIND, LEN, and SUBSTITUTE, which you can also learn more about here.

Find LEFT of a cell

=LEFT(A1,FIND("_",A1)-1)

This formula will look for the character _ in cell A1 and retrieve everything to the left of that character

Find RIGHT of a character (this only works if the character occurs once)

=RIGHT(A1,LEN(A1)-FIND("_",A1))

This formula will look for the character _ in cell A1 and retrieve everything to the right of that character

Find RIGHT of the last character (this works if there are multiple instances of the character you’re searching for)

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"","@",LEN(A1)-LEN(SUBSTITUTE(A1,"",""))),1))

This formula will look for the last _ in cell A1 and retrieve everything to the right of that character

Find MID of 2 unique characters

=MID(LEFT(A1,FIND("XYZ",A1)-1),FIND("ABC",A1)+1,LEN(A1))

This formula will look for and retrieve everything to the left of XYZ and everything to the right of ABC in cell A1

Find MID of 2 identical characters

=LEFT(MID(A1,FIND("",A1)+1,LEN(A1)),FIND("",MID(A1,FIND("_",A1)+1,LEN(A1)))-1)

This formula will look for and retrieve everything in between _ in cell A1

The goal of this blog is to answer the following queries.

  1. how to find characters to the right of a character in excel
  2. how to find characters to the right of a character in google sheets
  3. how to find characters to the left of a character in excel
  4. how to find characters to the left of a character in google sheets
  5. how do you get characters after characters in excel
  6. how do you get characters after characters in google sheets
  7. how do you find the position of a character in a cell in Excel
  8. how to use Excel RIGHT Function with examples
  9. how to use Excel LEFT Function with examples
  10. how to use Excel MID Function with examples
  11. how to use Excel LEN Function with examples
  12. how to use Excel SUBSTITUTE Function with examples

We’re building a better spreadsheet
Get your spot on the waitlist

Sign up for email updates