Can you remove contents from a cell in between ()

[Djblois]

I was going through my VBA book to look for ideas, clean up my code, and make my macro more useful when I came across StringElement. It sounds useful but the book doesn't explain it to well. Can I use it to get data from a cell between two Paranthesis ().

Daniel

[compariniaa]

I've never heard of StringElement, and I couldn't find it in the Object Library, but using VBA it is possible to take data from between two parenthesis. While I'm not great with VBA, I was able to get data from between two parenthesis using the following items:
range("A1").characters.text
range("A1").characters.count
an array
For...Next loop
If...End If
Think it through and you should get it

[compariniaa]

oh yeah, I also used
right$
left$

[Djblois]

I know I have already used right, left and mid. That is how I am doing it now but I want to make the code simpler and there are cases that the Paranthesis is in a different spot so left, mid, or right won't work then.

Daniel

[compariniaa]

use

range("A1").characters.text
range("A1").characters.count
For...Next loop
If...End If

to find the location of each parenthesis, and then store it into an array. then use the right and left

[OBP]

For finding the position of the "(" or the ")" use instring.You can then use the mid function.
You may need to use Andre's For/Next or other kind of loop if you are looking for more than one set.

[compariniaa]

I knew there must have been an easier way than the way I did it! thanks tony!

[Cyberdude]

And don't forget the magic of the "Split" function (see VBA Help). In the following example we want to extract "DE" from the string:

[vba]Sub Extract()
Dim X As String, Y As String, Ary1 As Variant, Ary2 As Variant
X = "ABC(DE)FG"
Ary1 = Split(X,")")
Ary2 = Split(Ary1(0), "(")
Y = Ary2(1)
MsgBox Y
End Sub[/vba]
This isn't an ideal application of Split, but it can be quite handy at times.