Finding the position of parentheses

[Pianoman23]

Hi guys.

I have a worksheet that contains around 90,000 rows.

Column A contains product descriptions imported from an outside source. Other columns contain codes and usage data, etc, and are not relevant here.

The descriptions in Col A vary insofar as the parentheses are not consistent; e.g.

[TABLE="width: 394"]
<tbody>[TR]
[TD="width: 394"]Abilify 5mg tablets (Otsuka Pharmaceuticals (U.K.) Ltd) 28 tablet 4 x 7 tablets[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 394"]
<tbody>[TR]
[TD="width: 394"]Able Spacer (Clement Clarke International Ltd) 1 device

[TABLE="width: 394"]
<tbody>[TR]
[TD="width: 394"]ActiLymph class 1 (18-21mmHg) below knee closed toe lymphoedema garment standard medium Sand (Activa Healthcare Ltd) 2 device[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

What I need to be able to do is find the position in the string of the first (; the second ( and similarly with ).

In some cases the bracketed content is removed and in some cases it is used; so, in the case of the above three items they should read:

[TABLE="width: 394"]
<tbody>[TR]
[TD="width: 394"]Abilify 5mg tablets 28 tablet 4 x 7 tablets[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 394"]
<tbody>[TR]
[TD="width: 394"]Able Spacer 1 device

[TABLE="width: 394"]
<tbody>[TR]
[TD="width: 394"]ActiLymph class 1 (18-21mmHg) below knee closed toe lymphoedema garment standard medium Sand 2 device

Can anyone help with a formula for this?

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

I think it is more to do with the length of text in the parentheses as Bosco's formula is not related to the "Ltd" text being there (which we don't yet know is a correct assumption). Bosco's formula removes the parentheses text and more if the final parentheses contains 10 or fewer characters - at least that's what I think.

Test with this OP example modified as shown
ActiLymph class 1 (18-21mmHg) below knee closed toe lymphoedema garment standard medium Sand (Activa Healthcare Ltd) 2 device
ActiLymph class 1 (18-21mmHg) below knee closed toe lymphoedema garment standard medium Sand (APlus Ltd) 2 device

Thanks for the correction, Peter. I had checked Bosco’s formula with some short words only, so I did not realize that it worked for longer substrings in brackets even if they were in end position.

 

[bosco_yip]

Test with this OP example modified as shown
ActiLymph class 1 (18-21mmHg) below knee closed toe lymphoedema garment standard medium Sand (Activa Healthcare Ltd) 2 device
ActiLymph class 1 (18-21mmHg) below knee closed toe lymphoedema garment standard medium Sand (APlus Ltd) 2 device

It can fix, but in longer length:

=IFERROR(SUBSTITUTE(A1,MID(A1,IF(-LOOKUP(,-FIND("(",A1,ROW($1:$99)))>FIND(")",A1),-LOOKUP(,-FIND("(",A1,ROW($1:$99))),FIND("(",A1)),2-LOOKUP(,-FIND(")",A1,ROW($1:$99)))-IF(-LOOKUP(,-FIND("(",A1,ROW($1:$99)))>FIND(")",A1),-LOOKUP(,-FIND("(",A1,ROW($1:$99))),FIND("(",A1))),),A1)&""

Regards