How to extract a particular set of string from a given set of sting?

[mailashish123]

[FONT=&quot]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:
[/FONT]

RAW DATA OUTPUT ABC (P) (L) (30365) ABC (P) (L) ABC (P) LTD (30365) ABC (P) LTD ABC P LTD (30365) ABC P LTD <colgroup><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

I have used this formula in cell B1
=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)))

[FONT=&quot]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.[/FONT]
[FONT=&quot]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.[/FONT]
[FONT=&quot]Can anyone help in giving a new formula/modify the above formula which will be dynamic in nature.[/FONT]

 

[sandy666]

you can try PowerQuery (Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.TransformColumns(Source, {{"RAW DATA", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}})
in
    Extract[/SIZE]

RAW DATA		RAW DATA
ABC (P) (L) (30365)		ABC (P) (L)
ABC (P) LTD (30365)		ABC (P) LTD
ABC P LTD (30365)		ABC P LTD

 

[FormR]

Hi, and here is a formula based option:

Excel 2013/2016
	A	B
1	RAW DATA	OUTPUT
2	ABC (P) (L) (30365)	ABC (P) (L)
3	ABC (P) LTD (30365)	ABC (P) LTD
4	ABC P LTD (30365)	ABC P LTD
5	ABC (a) (b) (d) (f) (a) P LTD (30365)	ABC (a) (b) (d) (f) (a) P LTD
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(LEFT(A2,FIND("|",SUBSTITUTE(A2,"(","|",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))-1))

 

[mailashish123]

Thanks!!

Solution verified!!!

 

[mailashish123]

Accepted!!!

 

[sandy666]

You are welcome

Have a nice day