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]

 

[XOR LX]

In some cases the bracketed content is removed and in some cases it is used

How will we know which applies for a given case?

Regards

 

[Peter_SSs]

Can anyone help with a formula for this?

I think a standard formula for this is a tall order, especially with the nested parentheses in the first example.

Further to XOR LX's question, the only thing I can see in these examples that distinguishes which () should be removed is that they all have "Ltd)" at the end.

The best I can do is this user-defined function that I think removes such sections of text..

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function RemoveP(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.IgnoreCase = True
  End If
  RX.Pattern = "\(([^\(\)]*(\([^\(\)]*\))*)*Ltd\)"
  RemoveP = Application.Trim(RX.Replace(s, ""))
End Function

Excel Workbook
	A	B
1
2	Abilify 5mg tablets (Otsuka Pharmaceuticals (U.K.) Ltd) 28 tablet 4 x 7 tablets	Abilify 5mg tablets 28 tablet 4 x 7 tablets
3	Able Spacer (Clement Clarke International Ltd) 1 device	Able Spacer 1 device
4	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 2 device
Remove (Some) Parentheses

 

[Pianoman23]

I think a standard formula for this is a tall order, especially with the nested parentheses in the first example.

Further to XOR LX's question, the only thing I can see in these examples that distinguishes which () should be removed is that they all have "Ltd)" at the end.

The best I can do is this user-defined function that I think removes such sections of text..

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function RemoveP(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.IgnoreCase = True
  End If
  RX.Pattern = "\(([^\(\)]*(\([^\(\)]*\))*)*Ltd\)"
  RemoveP = Application.Trim(RX.Replace(s, ""))
End Function

Remove (Some) Parentheses

*	A	B
*	*
Abilify 5mg tablets (Otsuka Pharmaceuticals (U.K.) Ltd) 28 tablet 4 x 7 tablets	Abilify 5mg tablets 28 tablet 4 x 7 tablets
Able Spacer (Clement Clarke International Ltd) 1 device	Able Spacer 1 device
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 2 device

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:826px;"><col style="width:668px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =RemoveP(A2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you so much. I never thought of removing the Ltd section of the nested parentheses.

I did come up with a somewhat convoluted way of doing it involving creating six additional columns to get the positions of the individual parentheses, then using the resulting positions to extract the final text from the string.

I'll be trying your method later today.

Thanks again.

 

[bosco_yip]

Herein a formula way, In B1 copy down :

=IFERROR(SUBSTITUTE(A1,MID(LEFT(A1,-LOOKUP(,-FIND(")",A1,ROW($1:$99)))),-LOOKUP(,-FIND("(",LEFT(A1,-LOOKUP(,-FIND(")",A1,ROW($1:$99)))-12),ROW($1:$99)))-1,99),),A1)

Regards

 

[Rick Rothstein]

For those who find Regular Expression solutions "hurt their heads", here is non-RegExp solution for you to consider (use Peter_SSs' assumption that the text Ltd is next to the closing parenthesis)...

Function RemoveP(s As String) As String
  Dim X As Long, LtdRight As Long, Paren As Long
  LtdRight = InStr(1, s, "ltd", vbTextCompare)
  For X = LtdRight - 1 To 1 Step -1
    If Mid(s, X, 1) = ")" Then
      Paren = Paren + 1
    ElseIf Mid(s, X, 1) = "(" Then
      If Paren Then
        Paren = Paren - 1
      ElseIf LtdRight Then
        RemoveP = Application.Trim(Left(s, X - 1) & Mid(s, LtdRight + 4))
        Exit Function
      End If
    End If
  Next
  RemoveP = s
End Function

 

[István Hirsch]

Herein a formula way, In B1 copy down :

=IFERROR(SUBSTITUTE(A1,MID(LEFT(A1,-LOOKUP(,-FIND(")",A1,ROW($1:$99)))),-LOOKUP(,-FIND("(",LEFT(A1,-LOOKUP(,-FIND(")",A1,ROW($1:$99)))-12),ROW($1:$99)))-1,99),),A1)

Regards

If the (*Ltd) string precedes another (no-Ltd) string, the latter is also removed.

 

[Peter_SSs]

If the (*Ltd) string precedes another (no-Ltd) string, the latter is also removed.

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

 

[Peter_SSs]

@Rick
I haven't had time to study your function in detail but I would suggest that it needs at least this change

LtdRight = InStr(1, s, "ltd)", vbTextCompare)

Otherwise, consider the consequences that the original string included a word like, say, "meltdown"

 

[Rick Rothstein]

@Rick
I haven't had time to study your function in detail but I would suggest that it needs at least this change

LtdRight = InStr(1, s, "ltd)", vbTextCompare)

Otherwise, consider the consequences that the original string included a word like, say, "meltdown"

@Peter_SSs,

The closing parenthesis was in the original code when I pasted it in, then I made a minor modification after posting it and perhaps the comment processor "ate it". I notice that every now and then stuff I type or paste gets changed... I think it has something to do with the automatic spell checking this forum's comment processor does. I don't know if that was the cause of the missing parenthesis or not, more than likely sloppy editing was to blame, but no matter what, thanks for noticing the omission.


@Pianoman (and other readers of this thread),

Here is Peter's correction to the code I posted originally in Message #6...

Function RemoveP(s As String) As String
  Dim X As Long, LtdRight As Long, Paren As Long
  LtdRight = InStr(1, s, "ltd)", vbTextCompare)
  For X = LtdRight - 1 To 1 Step -1
    If Mid(s, X, 1) = ")" Then
      Paren = Paren + 1
    ElseIf Mid(s, X, 1) = "(" Then
      If Paren Then
        Paren = Paren - 1
      ElseIf LtdRight Then
        RemoveP = Application.Trim(Left(s, X - 1) & Mid(s, LtdRight + 4))
        Exit Function
      End If
    End If
  Next
  RemoveP = s
End Function