PDA

View Full Version : Can you remove contents from a cell in between ()



Djblois
07-20-2006, 08:39 AM
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
07-20-2006, 09:22 AM
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
07-20-2006, 09:23 AM
oh yeah, I also used
right$
left$

Djblois
07-20-2006, 09:31 AM
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
07-20-2006, 09:36 AM
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
07-20-2006, 09:40 AM
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
07-20-2006, 09:46 AM
I knew there must have been an easier way than the way I did it! thanks tony! :thumb

Cyberdude
07-20-2006, 11:56 AM
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:

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
This isn't an ideal application of Split, but it can be quite handy at times.