1. ## obtain only the leading alphabetics of a string (W

Has anyone a neat procedure to obtain only the leading alphabetics of a string?

I need to extract "Mississauga" from "Mississauga's"
and to extract "She" from "She's"
and to extract "Timiskaming" from "Timiskaming-Cochrane"
and to extract "Terminal" from "Terminal2"
and so on for any known or unknown non-alphabetic.

I know I can write a loop that examines (MID\$) each character until one outside the range a-z or A-Z occurs.

The HELP screen for LIKE includes : "An exclamation point (!) at the beginning of charlist means that a match is made if any character except the characters in charlist is found in string."

so I'd be happy with an ultra-fast preprocessor that could alert me to a problem.

<pre>MsgBox "Mississauga's" Like "[!A-Za-z]"
</pre>

If I could get the above to tell me FALSE (as in "there is a non-alphabetic in there) I could use that to determine whether or not I should go into the Mid\$ loop.

I confess to being hopelessly confused with the LIKE operator.

2. ## Re: obtain only the leading alphabetics of a string (W

If each string had only one non alphabetic character you might have a look at using InStrRev mentioned by Legare here although Jan Karel has mentioned that the InStrRev is only included in Office 2000 and higher.

3. ## Re: obtain only the leading alphabetics of a string (W

1. You could step through the string and test for the ASCII value of each character to see if it fell outside the 65-90 or 97-122 range.
2. You could use this function and pass it a list of characters to test for:

<pre>Public Function Contains(ByVal varIn As Variant, _
ParamArray varFind() As Variant) As Boolean
'created by Charlotte Foust 2/3/2000
Dim intLoop As Integer
If Not IsNull(varIn) And _
Not IsEmpty(varFind) Then
For intLoop = 0 To UBound(varFind)
If InStr(varIn, varFind(intLoop)) <> 0 Then
Contains = True
Exit For
End If
Next intLoop
End If

End Function</pre>

4. ## Re: obtain only the leading alphabetics of a strin

Mike, thanks for the link, but no, the exclusion or delimiter character will be ANY character not in the original set. here's my first essay, it's a loop and I dislike it:

<pre>Public Function strStripAtFirstExclusion(ByVal strSource As String, strValid As String) As String
' Return the leading portion of strSource delimited by the first character not in strValid
Dim strResult As String
strResult = ""
Do While strSource <> ""
Dim strChar As String
strChar = Left\$(strSource, 1)
If InStr(1, strValid, strChar) > 0 Then ' a valid character
strResult = strResult & strChar
strSource = Right\$(strSource, Len(strSource) - 1)
Else
Exit Do
End If
Loop
strStripAtFirstExclusion = strResult
'Sub TESTstrStripAtFirstExclusion()
' MsgBox strStripAtFirstExclusion("", U.strcAlpha) ' RESULT: null string
' MsgBox strStripAtFirstExclusion("Mississauga", U.strcAlpha) ' RESULT: Mississauga
' MsgBox strStripAtFirstExclusion("Mississauga's", U.strcAlpha) ' RESULT: Mississauga
' MsgBox strStripAtFirstExclusion("Missi(ssauga's", U.strcAlpha) ' RESULT: Missi
' MsgBox strStripAtFirstExclusion("!@#\$%^&*()", U.strcAlpha) ' RESULT: null string
'End Sub
End Function
</pre>

5. ## Re: obtain only the leading alphabetics of a string (W

This UDF should do it:

Dim I As Long, iAsc As Integer
For I = 1 To Len(strSrc)
iAsc = Asc(UCase(Mid(strSrc, I, 1)))
If iAsc < 65 Or iAsc > 90 Then Exit For
Next I
GetLeading = Left(strSrc, I - 1)
End Function
</pre>

6. ## Re: obtain only the leading alphabetics of a string (W

In general, I've found that LIKE and other 'regular expression' usage to be slower than specific loops, because a specific loop KNOWS what it's looking for, whereas a regexp contains a LOT of machinery to enable it to look for everything.

Using the "simple" LIKE expression means you will go through the string at least once anyway, and if you find something 'interesting', you'll have to go thru it twice.

Why bother?

7. ## Re: obtain only the leading alphabetics of a strin

String concatenation is slow and you don't need to do it to find an index of the first invalid character. Try this:
<pre>Public Function strValidPrefix(ByVal strSource As String, strValid As String) As String
Dim lngCounter As Long
For lngCounter = 1 To Len(strSource)
If InStr(1, strValid, Mid\$(strSource, lngCounter, 1)) = 0 Then ' stop!
If lngCounter <> 1 Then
strValidPrefix = Left\$(strSource, lngCounter - 1)
Exit Function
Else
strValidPrefix = vbNullString
Exit Function
End If
End If
Next
strValidPrefix = strSource
End Function

Sub TESTstrValidPrefix()
Dim strAlpha As String
strAlpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvw xyz"
Debug.Print ">" & strValidPrefix("", strAlpha) & "<" ' RESULT: ><
Debug.Print ">" & strValidPrefix("Mississauga", strAlpha) & "<" ' RESULT: >Mississauga<
Debug.Print ">" & strValidPrefix("Mississauga's", strAlpha) & "<" ' RESULT: >Mississauga<
Debug.Print ">" & strValidPrefix("Missi(ssauga's", strAlpha) & "<"' RESULT: >Missi<
Debug.Print ">" & strValidPrefix("!@#\$%^&*()", strAlpha) & "<" ' RESULT: ><
End Sub</pre>

Renaming the function was purely an effort to make it fit in the horizontal space allotted by the Lounge. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

8. ## Re: obtain only the leading alphabetics of a string (W

I would simplify things by using WIN32 IsCharAlpha API to test for whether or not character is valid alphabetical character. The API is reportedly more efficient (ie quicker) than using user-defined VB functions for this purpose. Example:

Option Explicit

Declare Function apiIsCharAlpha Lib "user32" Alias "IsCharAlphaA" (ByVal cChar As Long) As Long

Public Function GetAlphaChars(ByVal strText As String) As String

Dim lngLen As Long
Dim n As Long
lngLen = Len(strText)

For n = 1 To lngLen
If apiIsCharAlpha(Asc(Mid(strText, n, 1))) = 0 Then
GetAlphaChars = Left\$(strText, n - 1)
Exit For
ElseIf n = lngLen Then ' All alphabetic chars
GetAlphaChars = strText
End If
Next n

End Function

Note that you should take into account possibility that text string being evaluated may not have any non-alphabetic characters. Some other useful API's for this type of thing:

Declare Function apiIsCharAlphaNumeric Lib "user32" Alias "IsCharAlphaNumericA" (ByVal cChar As Long) As Long
Declare Function apiIsCharUpper Lib "user32" Alias "IsCharUpperA" (ByVal cChar As Long) As Long
Declare Function apiIsCharLower Lib "user32" Alias "IsCharLowerA" (ByVal cChar As Long) As Long

These should be somewhat self-explanatory....

HTH

9. ## Re: obtain only the leading alphabetics of a strin

> For I = 1 To Len(strSrc)

Legare, thanks. About once a year you remind me (as did jefferson below) that using an integer loop is faster than continual manipulation of strings.

I like the function; it is concise and easy to read.

10. ## Re: obtain only the leading alphabetics of a strin

> a LOT of machinery to enable it to look for everything

Peter, I agree with you. I remember being HORRIFIED at what went on behind-the-scenes in some of the operating system or hardware code. It's the old thing of "what you don't see can't hurt you".

The defense for LIKE, or something like it, is that it might be easier to understand from a source maintenance point of view. LIKE has always been a stumbling block for me. My looping mentality is a relic of my FORTRAN days, and my string-habits are relics from my string-language days.

I know that I'm going to need to start timing some of this stuff; it was OK when i was playing around, but when a function gets buried in several levels of loops, the pico-wossits start to add up.

11. ## Re: obtain only the leading alphabetics of a strin

Jefferson, thanks. Your solution is a tad different from Legare's. In particular you are using a 32-bit long as an index. I seem to recall reading somewhere that longs were actually faster than integers, because the native storage is 32-bit. Is that the reason for your use?

12. ## Re: obtain only the leading alphabetics of a strin

> than using user-defined VB functions for this purpos

Mark, thanks for the contribution. (Great! Now I have FOUR methods to compare ......!).

I'd be interested to read the source of the supposition. I think of ASC as being a failry fast native sort of thing - it's not like a translation, it ought to be straight delivey of the binary encoding of the character, right?

13. ## Re: obtain only the leading alphabetics of a strin

> Is that the reason for your use?

<img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> No, Chris, I just don't trust you not to point the function at www.huge- <img src=/w3timages/censored.gif alt=censored border=0>-files.com and tell me there's an error in my code.

14. ## Re: obtain only the leading alphabetics of a string (W

Not too late to throw in a few cents worth of input I hope...

Your --> MsgBox "Mississauga's" Like "[!A-Za-z]"

got me thinking you were already exploring regular expressions to solve this
little item. Thinking maybe the ! did a NOT like the ^ does a NOT in regex. It don't.

But no need to shy away from the regex way...

Dim regex As Object
Set regex = CreateObject("vbscript.regexp")
regex.Pattern = "[^A-Za-z]"

MsgBox regex.Test("Kevins") -->false
MsgBox regex.Test("Kevin's") -->true
MsgBox regex.Test("Kevins2") -->true

set regex = nothing

Wouldn't be a bad idea to get into regular expressions...

p.s. client gotta have IE5.0 or later to get reg ex.

15. ## Re: obtain only the leading alphabetics of a strin

FWIW - I ran some tests that seem to support assertion that using API IsCharAlpha function is quicker than user-defined VB/VBA equivalent. Functions tested were WIN API:

Declare Function apiIsCharAlpha Lib "user32" Alias "IsCharAlphaA" (ByVal cChar As Long) As Long

And user-defined VB function, which is as simple as I could think of:

Public Function vbaIsCharAlpha(ByVal strChar As String) As Integer

Select Case Asc(strChar)
Case 65 To 90, 97 To 122
vbaIsCharAlpha = 1
Case Else
vbaIsCharAlpha = 0
End Select

End Function

Since the API function returns a Long (1 if true, 0 if false), the VB function likewise returns 1 or 0. In both cases the VB ASC function is used to convert text character to numerical equivalent. Sub used to test functions:

Declare Function apiGetTickCount Lib "kernel32" Alias "GetTickCount" () As Long

Public Sub TestIsAlphaFunction(ByVal strText As String, intFuncType As Integer)
On Error GoTo Err_Handler

Dim strMsg As String
Dim strFuncType(1 To 2) As String
Dim lngStart As Long
Dim lngElapsed As Long
Dim lngCounter As Long
Dim lngVal As Long
Dim i As Integer
Dim n As Long

strFuncType(1) = "IsCharAlpha API"
strFuncType(2) = "IsCharAlpha VBA"

lngStart = apiGetTickCount()
For i = 1 To 10000
For n = 1 To Len(strText)
Select Case intFuncType
Case 1
lngVal = lngVal + apiIsCharAlpha(Asc(Mid(strText, n, 1)))
Case 2
lngVal = lngVal + vbaIsCharAlpha(Mid(strText, n, 1))
End Select
lngCounter = lngCounter + 1
Next n
Next i
lngElapsed = (apiGetTickCount - lngStart)

Debug.Print " Function Name: " & strFuncType(intFuncType) & vbCrLf & _
" Loops: " & CStr(lngCounter) & " Value: " & CStr(lngVal) & vbCrLf & _
" Elapsed Time: " & CStr(lngElapsed) & " milliseconds"
Exit_Sub:
Exit Sub
Err_Handler:
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
Resume Exit_Sub

End Sub

Some representative results from Debug window:

(WIN 98, ACCESS 2K, P-III 900 MHZ 256 MB RAM):

TestIsAlphaFunction "ABC123",1
Function Name: IsCharAlpha API
Loops: 60000 Value: 30000
Elapsed Time: 99 milliseconds

TestIsAlphaFunction "ABC123",2
Function Name: IsCharAlpha VBA
Loops: 60000 Value: 30000
Elapsed Time: 161 milliseconds

(WIN XP, ACCESS 2002, P-IV 2 GHZ 512 MB RAM):

TestIsAlphaFunction "ABC123",1
Function Name: IsCharAlpha API
Loops: 60000 Value: 30000
Elapsed Time: 62 milliseconds

TestIsAlphaFunction "ABC123",2
Function Name: IsCharAlpha VBA
Loops: 60000 Value: 30000
Elapsed Time: 94 milliseconds

Testing with various text strings of varying length, on average the VBA function took 66.5% longer to execute, as measured by WIN GetTickCount API function, which returns the time elapsed (in milliseconds) since current Windows session commenced. The GetTickCount function is considered to be more accurate than the VB/VBA Timer function. I can offer no proof for the latter assertion, other than that is what I have read in references written by authors who are a lot smarter than I am.....

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•