Written by
Last updated on February 7, 2023
Download Example Workbook
Download the example workbook
This tutorial will demonstrate how to extract text between characters in a cell in Excel & Google Sheets.
Extract Text Between Parenthesis
To extract the text between any characters, use a formula with the MID and FIND functions.
Use this formula to extract the text between parenthesis:
=MID(B3,FIND("(",B3)+1,FIND(")",B3)-FIND("(",B3)-1)
The FIND Function locates the parenthesis and the MID Function returns the characters in between them.
FIND Functions
The first FIND function locates the position of the left parenthesis “(“. Add 1 to the result to return the character immediately after the left parenthesis.
=FIND("(",B3)+1
The second FIND function finds the position of the right parenthesis “)”.
=FIND(")",B3)The third FIND function is subtracted from the second FIND function, returning the length of text between the parentheses.
=FIND(")",B3)-FIND("(",B3)-1
MID Function
Both of these arguments’ values are entered into the MID function. This will give us the text between the parenthesis.
=MID(B3,C3,D3)
TRIM function
We can also add the TRIM Functionbefore the MID function to remove all extra spaces.
=TRIM(MID(B3,FIND("(",B3)+1,FIND(")",B3)-FIND("(",B3)-1))
Extract Text Between Characters In Google Sheets
The formula to extract text between characters works exactly the same in Google Sheets as in Excel:
I'm an expert in Excel and Google Sheets with extensive experience in utilizing various functions and formulas to manipulate data effectively. I have a deep understanding of Excel functions such as MID, FIND, TRIM, and their applications in extracting specific text within cells. Let me delve into the concepts mentioned in the article "Return to Excel Formulas List" to further illustrate these functions and their uses.
1. MID Function:
The MID function is used to extract a specific number of characters from a text string, starting at a specified position. For instance, in the formula =MID(B3, start_num, num_chars), B3 refers to the cell containing the text, start_num indicates the starting position of the text to extract, and num_chars specifies the number of characters to extract.
2. FIND Function:
The FIND function locates the position of a specific character or substring within a text string. For example, =FIND("(", B3) looks for the position of the left parenthesis "(" within cell B3. It returns the position of the first occurrence of the searched character.
3. TRIM Function:
The TRIM function removes extra spaces in a text string, leaving only single spaces between words and no leading or trailing spaces. It is commonly used to clean up text data imported from external sources.
4. Extracting Text Between Parentheses:
The article demonstrates how to extract text between parentheses using a combination of the MID and FIND functions. The formula =MID(B3, FIND("(", B3)+1, FIND(")", B3)-FIND("(", B3)-1) locates the positions of the opening and closing parentheses and extracts the text between them.
5. Application in Google Sheets:
The same formulas and functions used in Excel, such as MID, FIND, and TRIM, can be applied identically in Google Sheets for text manipulation and extraction.
By combining these functions in Excel or Google Sheets, users can efficiently extract specific text or substrings from cells based on defined criteria, such as characters or positions within the text. These functions offer versatile capabilities for data processing and analysis in spreadsheet applications.
