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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.