really need help with mid function 2

[Luis939]

I'm tryin to use the MID function to isolate a string in parenthesis inside a cell, such as blah (text) blahhhhhh. i just want to retrieve the work "text"
I tried
ActiveCell.Value = Mid(ActiveCell.Text, InStr(ActiveCell.Text, "(&quot + 1, InStr(ActiveCell.Text, &quot&quot - 1)

but it would only end up as text) blahh
i figured if i used the instr function correctly to remove everythign from the opening parenthesis to the beginning, then Id use the 2nd instr function correctly, but apparently it's working not from the original cell text, but after the first instr executes, thats why since the closing parenthisis is like 11-characters away from the beginning of the original cell, then my resulting string is text) blahh where the new string ends 11 characters away from the first parenthsis,
if you understand what i was just tryin to say then thats alright, i just need help in isolating just the content inside the parenthesis, thanks!

 

[VBAjedi]

I'm not sure about using the InStr function to do what you want, but here's a simple solution:

Use a little For. . .Next loop to find the parentheses and grab what's inside. For example

MyString = ActiveCell.Value
MyFlag = False
For x = 1 to Len(MyString)
  If Mid(MyString, x, 1) = "(" Then MyFlag = True
  If MyFlag = True then 
    MyNewString = MyNewString + Mid(MyString, x, 1)
  End If
  If Mid(MyString, x, 1) = ")" Then MyFlag = False
Next x
ActiveCell.Value = MyNewString

That should work as long as the cell contains both parentheses.

Let me know if this is what you were after!
VBAjedi

 

[SkipVought]

Hi,

Try this...

    With ActiveCell
        .Value = Mid(.Text, InStr(.Text, "(") + 1, Len(.Value) - InStr(.Text, ")") - 1)
    End With

Hope this helps
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Awww, MAN!

Skip, you're ALWAYS doin stuff better and faster than me!

LOL

I love it - that's how I learn.
VBAjedi

 

[JimRich]

The last parameter for the mid function is the length of the string. Looks to me as if you are doing the same instr function and moving back one instead of forward one. Try replacing the last instr statement with len(activecell.text).

 

[SkipVought]

Well 4335 was almost there with some good tight code.

He/She just needed to use the LEN function to get the length of the target string.

Also, using With ActiveCell, reduces the processing time since VB has to resolve 5 values all from the same node.

Thanx for the strokes You're no VBA slouch either!! Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

4335, I normally don't post after others have provided solutions, but in case you find (as I did) that the preceding "solutions" are less than optimal, you might want to try this:

Sub StripChars()
Dim FirstParen As Integer
Dim SecondParen As Integer
  FirstParen = InStr(ActiveCell.Text, "(")
  SecondParen = InStr(ActiveCell.Text, ")")
  If SecondParen - FirstParen > 0 Then
    ActiveCell.Value = Mid(ActiveCell.Text, _
         FirstParen + 1, SecondParen - FirstParen - 1)
  Else
    ActiveCell.Value = Mid(ActiveCell.Text, FirstParen + 1, 999)
  End If
End Sub

(Skip's code keeps some trailing junk, and VBAJedi's code keeps the parentheses.)

 

[Luis939]

wow thanks alot fellas, theres so much code there to examine for a newbie like me, but i really much appreciate all the help from all of you, it helps me learn so much better and faster thanks!

 

[SkipVought]

Zathras,
You're right. When I tested it, I did so placing the results in an adjacent cell (no residual) But replacing the value of the ActiveCell, does produce incorrect results.

KUDOS! Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com