Parse a string

[Sarah]

Does anyone know how I could parse a string that either looks like

RA010R01
or
R010R01

for the numeric string 010? This is not always the same. It could also
look like

R020R01

Thank you.

Sarah

 

[Michel Walsh]

LIKE "*010*"


or


LIKE "*020*"


as criteria should do, isn't it?

 

[JvC]

I'm guessing you want to extract the numeric portion, so
Public function GetNumbers(MyString as string) as string
dim Fragment as string
dim MyChar as string
MyChar = left(MyString, 1)
do while len(Fragment) < 3
if isnumeric(MyChar) and len(Fragment) < 3 then
Fragment = Fragment & MyChar
endif
MyString = right(MyString, Len(Mystring) - 1)
MyChar = left(MyString, 1)
loop
GetNumbers = Fragment
end function

This just works for extracting the 3 numerics. Other variations are
possible.

Sarah has brought this to us :

 

[raskew via AccessMonster.com]

Hi -

This may or may not be helpful, depending upon the structure possibilities of
your field.

Function GetNumer2(), shown below, will return the first numerical sequence
in a string, e.g.
? getNumer2("ABC123")
123

Problem being, real numbers don't begin with 0, so
? getNumer2("RA010R01")
returns 10, not 010

A workaround could be:
? "0" & getNumer2(("RA010R01")
which would return 010

...problem being, if your string could be something like "RA910RO1"
the previous would return 0910

As I say, it all depends on the possible string structures as to whether this
would be useful:

*******************************************************
Public Function GetNumer2(ByVal pStr As String) As Currency
'*******************************************
'Purpose: Returns the first numerical
' sequence in a string
'Coded by: raskew
'Inputs: ? getNumer2("ABC123")
'Output: 123
'*******************************************
Dim n As Integer
Dim strHold As String
Dim strKeep As String

strHold = Trim(pStr)
n = Len(strHold)
Do While n > 0
If val(strHold) > 0 Then
strKeep = val(strHold)
n = 0
Else
strHold = Mid(strHold, 2)
n = n - 1
End If
Loop

GetNumer2 = val(strKeep)

End Function
*******************************************************

HTH - Bob

 

[raskew via AccessMonster.com]

Back to the drawing board.

You could use the format() function to deal with both "010" and "910".
Examples:

x = "RA010R01"
? format(getNumer2(x), "000")
010

x = "RA910R01"
? format(getNumer2(x), "000")
910

These presumes that the 'numeric' string is three characters.

Bob

Hi -

This may or may not be helpful, depending upon the structure possibilities of
your field.

Function GetNumer2(), shown below, will return the first numerical sequence
in a string, e.g.
? getNumer2("ABC123")
123

Problem being, real numbers don't begin with 0, so
? getNumer2("RA010R01")
returns 10, not 010

A workaround could be:
? "0" & getNumer2(("RA010R01")
which would return 010

...problem being, if your string could be something like "RA910RO1"
the previous would return 0910

As I say, it all depends on the possible string structures as to whether this
would be useful:

*******************************************************
Public Function GetNumer2(ByVal pStr As String) As Currency
'*******************************************
'Purpose: Returns the first numerical
' sequence in a string
'Coded by: raskew
'Inputs: ? getNumer2("ABC123")
'Output: 123
'*******************************************
Dim n As Integer
Dim strHold As String
Dim strKeep As String

strHold = Trim(pStr)
n = Len(strHold)
Do While n > 0
If val(strHold) > 0 Then
strKeep = val(strHold)
n = 0
Else
strHold = Mid(strHold, 2)
n = n - 1
End If
Loop

GetNumer2 = val(strKeep)

End Function
*******************************************************

HTH - Bob

Does anyone know how I could parse a string that either looks like

[quoted text clipped - 10 lines]

Sarah

 

[Sarah]

Thank you. Yours is the best solution. Since the numeric portion can also
be 2 digits only. I can work with what you gave me.

Thanks again!

 

[John W. Vinson]

Does anyone know how I could parse a string that either looks like

RA010R01
or
R010R01

for the numeric string 010? This is not always the same. It could also
look like

R020R01

"Not always the same" is ambiguous. Might it be RRRR010R01 (010)? RA00025R01
(00025)?

What commonality is there? Will the number to be extracted always have three
digits?

 

[DaveT]

Nothing on TV so here goes. This gives results such as:

ParseIT("R0101R01") = 0101
ParseIT("R010R01") = 010

etc...


If the pattern is find first occurrence of a string of numbers within a
string then:

Public Function ParseIT(y)
Const strNumbers As String = "0123456789"

Dim strIN As String
Dim strTMP As String
Dim a As String

Dim zLen As Long

Dim iCounter As Long
Dim jCounter As Long

Dim blnFlag As Boolean

On Error GoTo TrapIt


If Len(Nz(y)) = 0 Then 'nothing to do
Exit Function

Else
strIN = y
zLen = Len(strIN)
End If

strTMP = ""
blnFlag = False

For iCounter = 1 To zLen
a = Mid(strIN, iCounter, 1)

If InStr(strNumbers, a) > 0 Then 'found first occurrence of number

strTMP = a

If iCounter = zLen Then 'rare case than only number is at end
Exit For
End If


For jCounter = iCounter + 1 To zLen 'run to end of string
a = Mid(strIN, jCounter, 1)

If InStr(strNumbers, a) > 0 Then
strTMP = strTMP & a

Else 'bail out if we find another alpha
blnFlag = True
Exit For
End If
Next jCounter


End If

If blnFlag Then Exit For

Next iCounter

ParseIT = strTMP

EnterHere:

Exit Function

TrapIt:
MsgBox Err.Number & vbCrLf & Err.Description
Resume EnterHere

End Function