You receive an export where every cell reads “Ana Perez (VIP) - Panama” and you only need the category inside the brackets to filter. It is four thousand rows and every name has a different length.
How to use Extract Text Formula step by step
1. Choose what to extract
A fixed number of characters from the left, right or middle, or whatever is left before, after or between symbols such as a hyphen, an at sign or a bracket.
2. Give the cell and the symbol
Type the text reference and, if needed, the character acting as the marker. The formula works out the positions for you.
3. Copy and drag
Paste the formula beside your data and drag it down. Afterwards you can paste the column as values to fix the text.
The Step Count: Traditional Friction vs. KorexTools
Cutting the text by hand cell by cell (4 steps)
- Count the characters up to the symbol.
- Write a MID with that fixed number.
- Discover the next name has a different length.
- Rewrite the formula for each case.
With the KorexTools “Extract Text Formula” builder (3 steps)
- You choose which piece you want.
- You give the cell and the symbol.
- You copy the formula and fill it down.
When the useful data sits inside other text, the key is locating the symbol
LEFT, RIGHT and MID work when the piece you want always has the same length and position, such as the first three characters of a code. If the length varies, you lean on FIND to learn where the hyphen, the at sign or the bracket is, and work out from there how many characters to take.
For the first word a space is added at the end of the text, so FIND always locates one even when the cell holds a single word. The last word needs the spaces inflated with REPT and trimmed from the right. With Microsoft 365, TEXTBEFORE and TEXTAFTER solve the same thing with far less text.
Which variant to use
- •Fixed length: LEFT, RIGHT or MID.
- •Variable length with a marker: before, after or between symbols.
- •Microsoft 365: TEXTBEFORE and TEXTAFTER, which are shorter.
The text “Ana Perez (VIP)” in A2
| What it extracts | Formula | Result |
|---|---|---|
| First word | =LEFT(A2,FIND(" ",A2&" ")-1) | Ana |
| Inside the bracket | =MID(A2,FIND("(",A2)+1,FIND(")",A2,FIND("(",A2)+1)-FIND("(",A2)-1) | VIP |
| Before the bracket | =LEFT(A2,FIND("(",A2)-1) | Ana Perez |
Common Mistakes and How to Avoid Them
- ⚠️Using LEFT with a fixed number when the length varies.
- ⚠️Forgetting the minus one and dragging the symbol itself into the result.
Frequently Asked Questions
Why do I get #VALUE! when extracting?
Because the symbol is missing in some cell and FIND cannot locate it. Wrap the formula in IFERROR or check the cells that lack the symbol.
Does FIND tell upper from lower case?
Yes. If you search a letter and do not care about its case, swap FIND for SEARCH in the generated formula.
Can I delete the original column after extracting?
Copy the formula column and paste it as values. That lets you delete the original column without the result changing.
What I would do if I were in your shoes
«Before extracting, look at two or three different cells: if the pattern does not repeat, no formula will fix it.» — DataKorex Engineering Team.