parse field

[traygo]

Is it possible to parse a field without knowing the number of
characters?

I want to sort a text field that is made up of a value that has a
number then Text then another number, such as:
27F821 or 1FD1222

where the first 'set' of charcters (the first number portion) will be
either one or two digit. The next 'set' (text) will be one or two
charcters (at least, I don't think there will be any that are more
than two characters), the last 'set' (number) will be 3 or 4 digit.

I'm not wanting to separate these into differenct fields, but am
wanting to do a custom sort by
first 'set' of numbers, last 'set' of numbers, middle text.

Is this possible without breaking the text into different fields?

Thanks, Josh

 

[Guest]

Josh:

Try the following functions:

Public Function StartPart(strIn As String) As Long

Dim n As Integer
Dim strOut As String
Dim strChr As String

For n = 1 To Len(strIn)
strChr = Mid(strIn, n, 1)
If IsNumeric(strChr) Then
strOut = strOut & strChr
Else
Exit For
End If
Next n

StartPart = Val(strOut)

End Function

Public Function MiddlePart(strIn As String) As String

Dim n As Integer
Dim strOut As String
Dim strChr As String

For n = 1 To Len(strIn)
strChr = Mid(strIn, n, 1)
If Not IsNumeric(strChr) Then
strOut = strOut & strChr
End If
Next n

MiddlePart = strOut

End Function

Public Function EndPart(strIn As String) As Long

Dim n As Integer
Dim strOut As String
Dim strChr As String

For n = Len(strIn) To 0 Step -1
strChr = Mid(strIn, n, 1)
If IsNumeric(strChr) Then
strOut = strChr & strOut
Else
Exit For
End If
Next n

EndPart = Val(strOut)

End Function

Ken Sheridan
Stafford, England

 

[Josh]

EXCELLENT!!

Tested, and does exactly what I need.

I only understand about a third of it, but I'll cogitate on it a while.

Thanks!

Josh

 

[Guest]

Here is a function that will do it all in one pass
Public Function SortString(strOriginal As String) As String
Dim strNumbers As String
Dim strLetters As String
Dim strTheChar As String
Dim lngCtr As Long

For lngCtr = 1 To Len(strOriginal)
strTheChar = Mid(strOriginal, lngCtr, 1)
If IsNumeric(strTheChar) Then
strNumbers = strNumbers & strTheChar
Else
strLetters = strLetters & strTheChar
End If
Next lngCtr

SortString = strNumbers & strLetters
End Function

 

[Josh]

It is treating the first 'set' as text, rather than Number. So that

10FD1222

is sorted before

1FD1222

Thanks, Josh

 

[Guest]

I have reposted the code below. Perhaps you didn't copy it over correctly.
I ran it and the results was 101222FD

I ran it like this from the immediate window:
?sortstring("10FD1222")

Also ran your original examples

?sortstring("27F821")
27821F
?sortstring("1FD1222")
11222FD

Your original request was
first 'set' of numbers, last 'set' of numbers, middle text.

Public Function SortString(strOriginal As String) As String
Dim strNumbers As String
Dim strLetters As String
Dim strTheChar As String
Dim lngCtr As Long

For lngCtr = 1 To Len(strOriginal)
strTheChar = Mid(strOriginal, lngCtr, 1)
If IsNumeric(strTheChar) Then
strNumbers = strNumbers & strTheChar
Else
strLetters = strLetters & strTheChar
End If
Next lngCtr

SortString = strNumbers & strLetters
End Function