Text String Manipulation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have text strings stored in a field called GLEXR that look like this:

AGN 380560 225007 2
AGN 35 299800 2

I'm looking for a function that would find and return the portion of the
string between the 2nd and 3rd space. In this example, the function would
return 225007 and 299800.

Any help would be appreciated!
 
How about this:

MyExtractedString = Mid(Left([GLEXR],InStrRev([GLEXR]," ")),InStr(InStr(1,
[GLEXR], " ") + 1, [GLEXR], " ") + 1)
 
I have text strings stored in a field called GLEXR that look like this:

AGN 380560 225007 2
AGN 35 299800 2

I'm looking for a function that would find and return the portion of the
string between the 2nd and 3rd space. In this example, the function would
return 225007 and 299800.

Any help would be appreciated!
Click to expand...

This is a good example of why you should follow the relational rule
that all fields should be atomic. It's a lot easier to concatenate
four fields than to tease one apart!

That said: you can use some very snarky nested Mid() and InStr() calls
to find it; or you can use some custom VBA code. Copy and paste the
code below into a new Module (save it as basUtilities or some name
OTHER than the name of the function); then in your Query use

Portion: GetToken([GLEXR], " ", 3, NULL)


Function GetToken(Package As String, Delimiter As String, Pos As
Integer, Missing As Variant) As Variant
' Comments :
' Parameters: Package
' Delimiter
' Pos
' Missing -
' Returns : Variant -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Accepts a string consisting of values separated by delimiters,
' and returns the "Pos" element. If there is no such element it
' returns the value in Missing.
On Error GoTo PROC_ERR


Dim iPos As Integer
Dim workstring As Variant
GetToken = Null
If Len(Package) = 0 Or Pos <= 0 Then
Exit Function
End If
workstring = Package
For Pos = 1 To Pos
GetToken = workstring
If IsNull(workstring) Then
Exit For
End If
iPos = InStr(workstring, Delimiter)
If iPos = 0 Then
workstring = Missing
Else
GetToken = Left(workstring, iPos - 1)
workstring = Mid(workstring, iPos + Len(Delimiter))
End If
Next Pos

Proc_Exit:
Exit Function

PROC_ERR:
MsgBox "Error " & Err.Number & " in GetToken:" _
& vbCrLf & Err.Description
Resume Proc_Exit

End Function



John W. Vinson[MVP]
 
Ken,

It's damn close - the problem is it leaves all the characters after the 3rd
space. In the original example below, the 2 (and all characters thereafter)
still remains.

Any other ideas?


Ken Snell said:
How about this:

MyExtractedString = Mid(Left([GLEXR],InStrRev([GLEXR]," ")),InStr(InStr(1,
[GLEXR], " ") + 1, [GLEXR], " ") + 1)

--

Ken Snell
<MS ACCESS MVP>

Kirk P. said:
I have text strings stored in a field called GLEXR that look like this:

AGN 380560 225007 2
AGN 35 299800 2

I'm looking for a function that would find and return the portion of the
string between the 2nd and 3rd space. In this example, the function would
return 225007 and 299800.

Any help would be appreciated!
Click to expand...
Click to expand...
 
John,

Your function works beautifully. Thanks for the help!



John Vinson said:
I have text strings stored in a field called GLEXR that look like this:

AGN 380560 225007 2
AGN 35 299800 2

I'm looking for a function that would find and return the portion of the
string between the 2nd and 3rd space. In this example, the function would
return 225007 and 299800.

Any help would be appreciated!
Click to expand...

This is a good example of why you should follow the relational rule
that all fields should be atomic. It's a lot easier to concatenate
four fields than to tease one apart!

That said: you can use some very snarky nested Mid() and InStr() calls
to find it; or you can use some custom VBA code. Copy and paste the
code below into a new Module (save it as basUtilities or some name
OTHER than the name of the function); then in your Query use

Portion: GetToken([GLEXR], " ", 3, NULL)


Function GetToken(Package As String, Delimiter As String, Pos As
Integer, Missing As Variant) As Variant
' Comments :
' Parameters: Package
' Delimiter
' Pos
' Missing -
' Returns : Variant -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Accepts a string consisting of values separated by delimiters,
' and returns the "Pos" element. If there is no such element it
' returns the value in Missing.
On Error GoTo PROC_ERR


Dim iPos As Integer
Dim workstring As Variant
GetToken = Null
If Len(Package) = 0 Or Pos <= 0 Then
Exit Function
End If
workstring = Package
For Pos = 1 To Pos
GetToken = workstring
If IsNull(workstring) Then
Exit For
End If
iPos = InStr(workstring, Delimiter)
If iPos = 0 Then
workstring = Missing
Else
GetToken = Left(workstring, iPos - 1)
workstring = Mid(workstring, iPos + Len(Delimiter))
End If
Next Pos

Proc_Exit:
Exit Function

PROC_ERR:
MsgBox "Error " & Err.Number & " in GetToken:" _
& vbCrLf & Err.Description
Resume Proc_Exit

End Function



John W. Vinson[MVP]
Click to expand...
 
Is there a space at the end of the text string? I tested this expression in
the Immediate Window (I removed the [ ] characters for the testing):


Test One:
GLEXR="AGN 380560 225007 2"

?Trim(Mid(Left(GLEXR,InStrRev(GLEXR," ")),InStr(InStr(1, GLEXR, " ") + 1,
GLEXR, " ") + 1))
225007


Test Two:
GLEXR="AGN 380560 225007 2 "

?Trim(Mid(Left(GLEXR,InStrRev(GLEXR," ")),InStr(InStr(1, GLEXR, " ") + 1,
GLEXR, " ") + 1))
225007 2


If there is a trailing space, then this expression should work:
Trim(Mid(Left(Trim(GLEXR),InStrRev(Trim(GLEXR)," ")),InStr(InStr(1,
Trim(GLEXR), " ") + 1, Trim(GLEXR), " ") + 1))


--

Ken Snell
<MS ACCESS MVP>


Kirk P. said:
Ken,

It's damn close - the problem is it leaves all the characters after the
3rd
space. In the original example below, the 2 (and all characters
thereafter)
still remains.

Any other ideas?


Ken Snell said:
How about this:

MyExtractedString = Mid(Left([GLEXR],InStrRev([GLEXR],"
")),InStr(InStr(1,
[GLEXR], " ") + 1, [GLEXR], " ") + 1)

--

Ken Snell
<MS ACCESS MVP>

Kirk P. said:
I have text strings stored in a field called GLEXR that look like this:

AGN 380560 225007 2
AGN 35 299800 2

I'm looking for a function that would find and return the portion of
the
string between the 2nd and 3rd space. In this example, the function
would
return 225007 and 299800.

Any help would be appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top