Let us assume, I have some data in the first column in my Excel worksheet. I want the remove the parentheses and the text within the parentheses.
Birds
Hawk (Bald Eagle) 002
Dove (Morning Dove) 005
Sparrow (Canyon Towhee) 009
Woodpecker (Gilded Flicker) 012
After removing the braces (or the parentheses) and the text within, this is what I should get.
Hawk 002
Dove 005 etc.
The Formula
Write the below formula in the 2nd column.
=TEXTJOIN("", TRUE, LEFT(A2, FIND("(", A2) - 1), MID(A2, FIND(")", A2) + 1, LEN(A2) - FIND(")", A2)))
The formula uses a combination of few Excel built-in functions, most importantly "TEXTJOIN()", "LEFT()" and "MID()" functions.
TEXTJOIN() function
The TEXTJOIN() function is used to combine text from multiple strings (or range), with a delimiter (like a comma) to separate the text. However, I am not using any "delimiter" in the above formula, because I want the texts only.
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
* delimiter: A "delimiter" will be a text or character that you can use to separate the text. There is no delimiter in the formula.
* ignore_empty: A TRUE or FALSE that specifies whether to ignore empty cells.
* text1, text2: The text or values that you want join together. To get values for "text1" and "text2" arguments, I have used the "LEFT()" and "MID()" functions.
=TEXTJOIN("", TRUE, LEFT(...), MID(...))
LEFT() function
Syntax
LEFT(text, [num_chars])
The LEFT() function extracts texts or a specified number of characters from left side of a string.
* text: the string from which you want to extract characters.
* num_chars: Number of characters you want the function to extract.
This is how the LEFT() function in the above formula is used:
LEFT(A2, FIND("(", A2) - 1)It extracts the texts from left side of the cell (A2) up to the character before the opening "(" bracket (parenthesis).
MID() function
Syntax
MID(text, start_num, num_chars)
The MID() function extracts a specified number of characters from a string, starting a particular position.
* text: the string from which you want to extract characters.
* start_num: A number or integer value denoting the position of the first character you want to extract.
* num_chars: The number of characters you want the function to extract from the string.
This is how the MID() function in above formula is used:
MID(A2, FIND(")", A2) + 1, LEN(A2) - FIND(")", A2))The function extracts the text from the string after the closing ")" bracket (parentheses) to the end of the string in cell A2.