Take the text between parentheses
PremiumIn B2, take the department between the parentheses in A2. Use TEXTAFTER to take what comes after "(", and TEXTBEFORE around it to stop at ")".
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Department | ||||
| 2 | Laura Chen (Marketing) | |||||
| 3 | Sam Ortiz (IT) | |||||
| 4 | Priya Shah (Customer Service) | |||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock SUBSTITUTE, TEXTBEFORE and TEXTAFTER and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for admin jobs
Why this works
TEXTAFTER(A2,"(") gives "Marketing)", everything after the opening parenthesis. TEXTBEFORE then cuts that off at ")", leaving "Marketing". The department can be any length, so the formula works for every row.
Common mistake: Using MID with a fixed start and length. The names and departments have different lengths, so the cut lands in the wrong place on the next row.
The answer
- B2
=TEXTBEFORE(1TEXTAFTER(A2,"("),2")")
In plain words: Take 1everything after the ( in A2 and stop 2at the ).