mailashish123
New Member
- Joined
- Mar 14, 2019
- Messages
- 12
[FONT="]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]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 318"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]RAW DATA[/TD]
[TD]OUTPUT[/TD]
[/TR]
[TR]
[TD]ABC (P) (L) (30365)[/TD]
[TD]ABC (P) (L) [/TD]
[/TR]
[TR]
[TD]ABC (P) LTD (30365)[/TD]
[TD]ABC (P) LTD [/TD]
[/TR]
[TR]
[TD]ABC P LTD (30365)[/TD]
[TD]ABC P LTD [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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="]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="]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="]Can anyone help in giving a new formula/modify the above formula which will be dynamic in nature.[/FONT]
[/FONT]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 318"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]RAW DATA[/TD]
[TD]OUTPUT[/TD]
[/TR]
[TR]
[TD]ABC (P) (L) (30365)[/TD]
[TD]ABC (P) (L) [/TD]
[/TR]
[TR]
[TD]ABC (P) LTD (30365)[/TD]
[TD]ABC (P) LTD [/TD]
[/TR]
[TR]
[TD]ABC P LTD (30365)[/TD]
[TD]ABC P LTD [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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="]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="]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="]Can anyone help in giving a new formula/modify the above formula which will be dynamic in nature.[/FONT]