ExcelInsider Team
In a production environment, some cells in an excel sheet can sometimes be created with some ID or data inside a string. A customer might write the order requirements on a sheet, and you need to extract the exact values using some delimiters. In this article, we will learn six ways to extract text between two characters in excel.
➤ Write this formula in a helper cell:
=TEXTBEFORE(TEXTAFTER(B2, “’”), “’”)
➤ Replace B2 with the source cell, first quote (‘) with the first character, and second (‘) with the second character.
➤ Press Enter
That was one short and simple way to extract text between two characters. However, there are many other ways to do the job. We will discuss them below.
Extracting Text Inside Two Quotes
In this example, we have some product data along with their price, color, and other information. In the description column, the colors are written inside single quotes (‘). We need to extract the colors into another column for better comprehension.
Here are the steps to do the job:
➤ We will take column E to do the job. In the E2 cell, we will write this formula:
=TEXTBEFORE(TEXTAFTER(B2, “‘”), “‘”)
➤ Press Enter and autofill other cells.
Note:
These functions only work on Microsoft 365. If you work in an older office, choose one of the following methods.
Combining MID & SEARCH Functions
A lot of users are using older office versions, and the functions used before are not suitable for them. However, the formula used in this method will surely help them.
➤ Add a column for the colors. In the cell right below the heading, write this formula:
==MID(B2, SEARCH(“‘”, B2) +1, SEARCH(“‘”, B2, SEARCH(“‘”,B2) +1) – SEARCH(“‘”, B2) -1)
➤ Autofill other cells using your mouse.
Extracting Numbers Inside Two Characters
In the above examples, we have used functions that do not care whether the first character is the same as the second one. However, this one does. It might seem a little limited compared to those, but it’s still good to know in case you need the formula someday.
➤ Enter the following formula in a helper cell:
=MID(LEFT(A2,FIND(“]”,A2)-1),FIND(“[“,A2)+1,LEN(A2))
➤ Press Enter
Using Flash Fill
This is probably the easiest method if you have to extract text from multiple cells in a column. However, it does not work if you want to do it for a singular cell.
➤ Take a helper column with the same rows.
➤ In the first row after the heading, manually write the middle text that is in the same row. In this example, the source cell contains Widget A ‘Red’, so we will write Red
➤ Press Enter to go to the next cell.
➤ Go to the Data In the Data Tools section, click the icon shown in the image:
➤ Now, all of the cells in the column will automatically be filled with your desired text.
Combining SUBSTITUTE-MID-REPT Functions
This method can only be used if the text you want to extract belongs inside two characters that are the same. This is how you do it:
➤ Enter this formula:
=SUBSTITUTE(MID(SUBSTITUTE(“‘” & B2&REPT(” “,6),”‘”,REPT(“,”,255)),2*255,255),”,”,””)
➤ Press Enter, and if you want, autofill other cells.
Inserting RIGHT-IF-REPLACE-LEN-SEARCH-SUBSTITUTE Functions
This one is a long formula and can only be used if both of the characters you are trying to parse are the same. Here is the guide to using the formula:
➤ Enter this formula:
=RIGHT(IF(RIGHT(B2)=”‘”,REPLACE(B2,LEN(B2),1,””),B2),LEN(B2)-SEARCH(“@”,SUBSTITUTE(B2,”‘”,”@”,LEN(B2)-LEN(SUBSTITUTE(B2,”‘”,””))-(RIGHT(B2)=”‘”)),1)-(RIGHT(B2)=”‘”))
➤ Move on to autofill other cells.
Note:
If your string has the at sign (@), this formula will not work.
Frequently Asked Questions
How do I extract text from the middle of a string in Excel?
Use the MID function:
=MID(A1,5,4)
Replace the first parameter with your cell, the second parameter with the position, and third parameter with how many characters you want to extract.
How to concatenate in Excel?
Use the CONCAT function:
=CONCAT(A1,A2)
This function merges A1 and A2 cells.
How to merge two cells in Excel?
Select the cells you want to merge. Then from the Home tab, go to Alignment > Merge Cells.
How to cut text in Excel?
You can use keyboard shortcuts like Ctrl+X, like any other program.
How do I separate text from text in Excel?
The LEFT, RIGHT, MID, LEN, and SEARCH functions will help with that:
Wrapping Up
In this article, we have learned numerous ways to extract text between two characters in excel. Download the file we included to practice more, and comment below which method is the one you ended up using.
