Step through Characters in a String

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

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



--
Regards,
Tom Ogilvy

"Izran" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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


MPManzi

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

"Izran" wrote:

> 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
>
>

>> 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. :>)
--
Dana DeLouis
Win XP & Office 2003


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Izran" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

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


"Izran" <[email protected]> wrote in message
news:[email protected]...
> 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
>