Step through Characters in a String

[Izran]

My problem is this: I have a spreadsheet I need to sort, but the
information to sort by is contained at the end of a text string in a
cell between parentheses.

example: Utility Busway E57 - E81 Busplug (P001)

P001 is the information I need to extract from that string. Not all
cells in a particular column that are to be looked at contain the
(****) and the lack of (***) will have me look at another column which
will contain the information. The number of characters before the open
parentheses is not constant nor is the the number of characters between
the open and close parentheses.

Is there a way to just step through the characters of a string?? My
previous attempts to do this haven't worked and I have managed to find
work arounds using other functions. The open parentheses is the flag
to indicate that the following characters are to be extracted until a
closed parentheses is reached.

Thank you,
MPManzi

 

[Tom Ogilvy]

I assume you mean you have used built in functions like find/search and
other string functions and are asking how to loop through a string in VBA
and extract characters between the parentheses

Public Function ExtractChar(sStr1 As String)
Dim i As Long, sStr As String
Dim sChr As String
For i = 1 To Len(sStr1)
sChr = Mid(sStr1, i, 1)
Select Case sChr
Case "("
sStr = ""
bcollect = True
Case ")"
bcollect = False
Case Else
If bcollect Then
sStr = sStr & sChr
End If
End Select
Next
ExtractChar = sStr

End Function

as a demonstration of usage from the immediate window:

s = ExtractChar("Utility Busway E57 - E81 Busplug (P001)")
? s
P001

 

[Izran]

Thank you very much Tom. You're assumption was correct.


MPManzi

 

[Guest]

Hi,
This UDF will return string between "(" and ") ".

For example, if data is in cell A1 then in b1 put =FindKey(a1)

Or in VBA

Mykey=FindKey(range("a1"))

HTH

Function FindKey(rng) As String
MyStr = rng.Value
st = InStr(1, rng.Value, "(")
ft = InStr(1, rng.Value, ")")
If st = 0 Then
FindKey = ""
Else
FindKey = Mid(MyStr, st + 1, ft - st - 1)
End If
End Function

 

[Dana DeLouis]

Is there a way to just step through the characters of a string??

If you would like something a little different, then here is a Regular
Expression. It's probably a little slower than the other excellent examples
though.
In vba, you would do Tools | Reference | and set a library reference to:
''Microsoft VBScript Regular Expressions 5.5

Option Explicit
Dim RE As RegExp
' = = = = = = = = =

Function ExtractParenthesis(S) As String
'// Set RE usually called only once...
If RE Is Nothing Then
Set RE = New RegExp
RE.IgnoreCase = True
RE.Global = True
End If

RE.Pattern = "\((\w+)\)"
If RE.Test(S) Then
ExtractParenthesis = RE.Execute(S)(0).SubMatches(0)
End If
End Function


s = "Utility Busway E57 - E81 Busplug (P001)"

? ExtractParenthesis(s)
P001

HTH. :>)

 

[Peter T]

One more just for luck -

Function inBrackets(strIn As String) As String
Dim i As Long, j As Long, k As Long
Dim bArr() As Byte
Dim bArr2() As Byte
' won't work in Mac

' chr(40) = "(", chr(41) = ")"

bArr = StrConv(strIn, vbFromUnicode)

For i = LBound(bArr) To UBound(bArr)

If bArr(i) = 40 Then
j = i
Do
j = j + 1
Loop Until (bArr(j) = 41 Or j = UBound(bArr))

If bArr(j) = 41 Then

ReDim bArr2(i + 1 To j - 1)
For k = i + 1 To j - 1
bArr2(k) = bArr(k)
Next

inBrackets = StrConv(bArr2, vbUnicode)
Else
' inBrackets = "no closing )"
End If

Exit For
End If
Next
' If j = 0 Then inBrackets = "no opening ("
End Function

Regards,
Peter T