Solved: Get length of string inside parentheses

[Bouldergirl]

Hello,

I've got a bunch of cells which contain long, variable-length strings of data, and nested inside the string is a set of parentheses.

e.g.: this+isAll&unnecessary" stuff">Idon'tneed>(but this part I need)<followed by&MORE unnecessarySTUFF

So I ONLY want to count the number of characters inside the parentheses (i.e., the function I'm looking for in the above example would count "but this part I need", and would return 20)

The parentheses don't appear in exactly the same place within each cell. I'm thinking that using the "FIND" function will be a part of the solution, or maybe I need to get a VBA module, I don't know. Thanks in advance!

Any suggestions?

Thanks!
-Bouldergirl

[GTO]

I would think a formula might be neat, but as I'm more 'formula challenged' than 'vba challenged', here's a shot:


Option Explicit

Sub RetCounts()
Dim REX As Object,
Dim rngData As Range,
Dim aryIn As Variant,
Dim aryOut As Variant,
Dim i As Long

'// Change sheetname and range to suit//
With ThisWorkbook.Worksheets("Sheet1")
Set rngData = Range(.Range("A2"), .Cells(Rows.Count, 1).End(xlUp))
End With

aryIn = rngData.Value
ReDim aryOut(1 To UBound(aryIn, 1), 1 To 1)

Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.Pattern = ".*?\(|\).*"

For i = LBound(aryIn, 1) To UBound(aryIn, 1)
If .Test(aryIn(i, 1)) Then
aryOut(i, 1) = Len(Trim(.Replace(aryIn(i, 1), vbNullString)))
Else
aryOut(i, 1) = 0
End If
Next
End With

rngData.Offset(, 1).Value = aryOut
End Sub
Mark

[mbarron]

Using the Find function
=FIND(")",A1)-FIND("(",A1)-1

[Dave]

GTO could you add some comment to your code? I would like to learn abit more about the use of VBScript.RegExp. I would have gone for something like this which is probably slower. Dave

Sub StrLen()
Dim Flag As Boolean, Lastrow As Integer, Cnt As Integer
Dim i As Integer, OutputCnt As Integer
Flag = False 'counter switch
Lastrow = 10 'row length adjust
OutputCnt = 0
For Cnt = 1 To Lastrow 'loop rows
'loop cell contents
For i = 1 To Len(Sheets("Sheet1").Range("A" & Cnt).Value)
If Flag = False Then
'search for "(" (ie. Asc 40) as start of search text
If Asc(Mid(Sheets("Sheet1").Range("A" & Cnt).Value, i, 1)) = 40 Then
Flag = True
End If
Else
'search for ")" (ie. Asc 40) as end of search text
If Asc(Mid(Sheets("Sheet1").Range("A" & Cnt).Value, i, 1)) = 41 Then
'Sheets("Sheet1").Range("B" & Cnt).Value = OutputCnt
MsgBox "This many letters: " & OutputCnt
Flag = False
OutputCnt = 0
Exit For
End If
OutputCnt = OutputCnt + 1
End If
Next i
Next Cnt
End Sub

[GTO]

Hi Dave,

Its late and I'm having problems posting, and it took a while to find some links that you may wish to gander. I'll try and comment the code up later today/tomorrow, but before I lose 'em...

http://msdn.microsoft.com/en-us/library/1400241x(VS.85).aspx
http://www.aivosto.com/vbtips/regex.html
http://www.regular-expressions.info/tutorial.html

Have a great day,

Mark

[Dave]

Thanks Mark for your time in finding and posting those excellent informative links. I'm still having some difficulty following how you used the array to contain the output. Have a nice day. Dave

[Bouldergirl]

Thanks to everyone for your replies. :clap: When I stated that I wanted to count the number of characters inside of the parentheses, it was because what I really wanted to do was create a column of cells populated with the actual text within the parentheses, and not all the other gobbledy-gook (gotta love those big tech words) in the cell. I had already found a formula using substitute, and the problem I was having was simply telling excel how many characters past the first parenthesis to print. So, while I think the VBA is great, I found that mbarron's find function did just the trick, employed as such:

=SUBSTITUTE(MID(A7,FIND("(",A1)+1,(FIND(")",A1)-FIND("(",A7)-1)),")","")

where A1 (obviously) represents the cell containing the text I want to manipulate.

Cheers,
-Tiffany

[lucas]

Tiffany, be sure to mark your thread solved using the thread tools at the top of the page.

That will keep others from reading an entire thread just to find that it's already been solved.

Just a courtesy.

[Bouldergirl]

Thanks, Steve; still learnin' the ropes.