I need to find specific text in a column in PowerPivot but the field is sometimes spelt incorrectly.
EG:
| Data | Open/Closed |
|---|---|
| (Cloosed) Emp 1 | |
| Emp 2 | |
| (Account Closeeed) Emp 3 | |
| (CLOSED) Emp 4 | |
| (Closeed) Emp 5 | |
| Emp 6 (CLD FO) |
The only consistent thing i can use is:
Look within the first set of brackets
Must have
"(*cl*ed)"
I have used this :
=IF(SEARCH("(*cl*ed)", _EmpData[DATA], 1, 0) >0,"CLOSED","OPEN")
I need to change this to only look in the first set of brackets as you can see from the above example table some data has a second set of brackets in which is being picked up when it shouldnt
Can someone advise on how to only search if the first character in the cell is a ( please?