1

I am trying to figure out how to remove all data [including opening parenthesis "("] which is appearing after the last occurrence of opening parenthesis "(" in a given string. Refer below example:

container
RAW DATA                OUTPUT
ABC (P) (LTD) (30365)   ABC (P) (LTD)
ABC (P) LTD (30365)     ABC (P) LTD
ABC P LTD (30365)       ABC P LTD

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))=3,LEFT(A1,FIND("(",A1,FIND("(",A1,FIND("(",A1)+1)+1)-1),IF(LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))=2,LEFT(A1,FIND("(",A1,FIND("(",A1)+1)-1),LEFT(A1,FIND("(",A1)-1)))

I have used the logic in deriving the above formula that how many times "(" is appearing in a text. I have assumed that maximum 3 times "(" this will come so i have used above formula.

The only problem is that the above formula is not dynamic. For example, if the string contains opening parenthesis "(" six times then this formula will not give the desired result.

Can anyone help in giving a new formula/modify the above formula which will be dynamic in nature.

GSerg
  • 76,472
  • 17
  • 159
  • 346
mailashish123
  • 15
  • 4
  • Take the "find from end" function from https://stackoverflow.com/q/350264/11683. Replace the space with `"("`. Replace the `RIGHT` with `LEFT` and remove the `LEN(A1)-`. – GSerg Jun 14 '19 at 08:51

1 Answers1

1

Try this

=LEFT(A15,FIND("@",SUBSTITUTE(A15,"(","@",LEN(A15)-LEN(SUBSTITUTE(A15,"(",""))),1)-2)
JLCH
  • 793
  • 1
  • 9
  • 15