Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>

    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    This UDF should do it:

    <pre>Public Function GetLeading(strSrc) As String
    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>

    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    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. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    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. #14
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  15. #15
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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 LastLast

Posting Permissions

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