Macro Remove Characters From String

[boldcode]

Hello,

I want to create a Macro that will remove all characters after the second bracket in my string contained in Column H in the Active Sheet. I want to use vba code because this code will be combined with another piece of code.

Here is an example of my data:

What the data looks like Before Macro is executed:

<table border="0" cellpadding="0" cellspacing="0" width="375"><colgroup><col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:10605;width:218pt" width="290"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:64pt" height="17" width="85">
</td> <td style="width:218pt" width="290">Column H</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 1</td> <td>Codes</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 2</td> <td>[0567J]</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 3</td> <td>[0398AP]</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 4</td> <td>[08905M];[081P];[081897V];[0837865L];[086Y]</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 5</td> <td>[08780L];[08567A];[087A]</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 6</td> <td>[1456Q]</td> </tr> </tbody></table>
After Macro is run the data will look like this:

<table border="0" cellpadding="0" cellspacing="0" width="375"><colgroup><col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:10605;width:218pt" width="290"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:64pt" height="17" width="85">
</td> <td style="width:218pt" width="290">Column H</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 1</td> <td>Codes</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 2</td> <td>[0567J]</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 3</td> <td>[0398AP]</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 4</td> <td>[08905M]</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 5</td> <td>[08780L]</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">Row 6</td> <td>[1456Q]</td> </tr> </tbody></table>

 

[VoG]

Try

Sub ATest()
Dim LR As Long, i As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("H" & i)
        .Value = Left(.Value, InStr(.Value, "]"))
    End With
Next i
End Sub

 

[boldcode]

VoG,

That worked just the way I wanted it to work. Thank You.

BC

 

[fergusmacdonald]

Any idea how this could be converted to strip the T and Timestamp from values that look like this: 2013-01-08T21:17:04+00:00. IE I want to be left with only the date in the column so need to delete the T21:17:04+00:00 from all the cells in column C.

I've updated it as follows, but it leaves the T as the last value of the date changes on each line, the T is the first constant character, but it needs removed as well.

Sub StripTimestamp()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("C" & i)
        .Value = Left(.Value, InStr(.Value, "T"))
    End With
Next i
End Sub

Any help much appreciated.

 

[BrianMH]

Sub StripTimestamp()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("C" & i)
        .Value = Left(.Value, InStr(.Value, "T")-1)
    End With
Next i
End Sub

 

[fergusmacdonald]

Works beautifully! Thank you very much.

 

[Rick Rothstein]

Works beautifully! Thank you very much.

If you are interested, here are the same two macros (I used the same names) modified to run without using a loop...

Sub ATest()
  Dim Addr As String
  Addr = "H2:H" & Range("H" & Rows.Count).End(xlUp).Row
  Range(Addr) = Evaluate("IF(LEN(" & Addr & "),LEFT(" & Addr & ",FIND(""]""," & Addr & "&""]"")),"""")")
End Sub

Sub StripTimestamp()
  Dim Addr As String
  Addr = "C2:C" & Range("C" & Rows.Count).End(xlUp).Row
  Range(Addr) = Evaluate("IF(LEN(" & Addr & "),LEFT(" & Addr & ",FIND(""T""," & Addr & "&""T"")-1),"""")")
End Sub

 

[fergusmacdonald]

Thanks. What's the advantage of having them without using a loop?