Find the Last letter in a string containing letters and numbers

[DebbieCoates]

HI

I am trying to find the last letter within a string.

I have a field which contains data like CV8 7TY0124 121212

I am trying to extract everything prior to the first letter, ie CV8 7TY

I am thinking about finding the last letter within a string, then using the right, left or Mid funtion to grab the characters I want. Am I on the right lines?

Would appreciate any help

[g4hsean]

Ok it seem's to me like its a zip code if so you could mid$ (string,1,7). But if you want the rest after the last letter being
example string = CV8 7TY
Last letter = Y<---------------------------

then what you do is you Mid$(String,7,Len(string)) but that's only if you want letters or numbers after the CV8 7TY. Please tell me if that's what you wanted because your post is not exactly clear.

P.S Hope i helped you

[schoolbusdriver]

I am trying to extract everything prior to the first letter, ie CV8 7TY

I assume you mean "prior to the last letter"

It would be faster using InstrRev and checking for the first character which isn't a space or number (Val) then using Left. (assuming you don't want to return a fixed length string. Is it a car number plate?)

[purplegerbil]

This code will search through a given string looking for a letter. if found the string will be split into two halves.

I used, 1 text, 1 command and 2 labels.

Option Explicit
 
Private Sub Command1_Click()
 
    Dim inString As String      ' Input string
    Dim leftString As String
    Dim rightString As String
    Dim pos As Integer          ' Position within input string
    Dim found As Boolean        ' if found character flag
    Dim tmpChar As String       ' single character within string
    
    inString = Text1.Text       ' get info from text box
    found = False               ' set flag
    pos = Len(inString)         ' get start position, last character in string
    
    
    ' Loop through string checking each character
    Do
        tmpChar = Mid$(UCase(inString), pos, 1) ' get character
        
        ' check if a letter, if so, change flag to true
        If Asc(tmpChar) >= 65 And Asc(tmpChar) <= 90 Then found = True
    
        ' move to next character
        pos = pos - 1
    Loop Until found = True Or pos = 0
    
    If found = True Then pos = pos + 1
        
    leftString = Left$(inString, pos)
    rightString = Right$(inString, Len(inString) - pos)
    
    ' output to user
    Label1 = leftString
    Label2 = rightString
    
 
End Sub

Hope this helps.

pG

[g4hsean]

Yea anyway's good hope its what the persone want's and i forgot to add the string reverse.

[schoolbusdriver]

I forgot something too, g4hsean. Checking zero with val. The search will probably have to test for "0" too!

[smUX]

Is it a car number plate?

It's a UK postcode (US equivalent is zipcode) and phone number

[bushmobile]

A For Next loop would probably be more appropriate in this situation:

Private Function Extract(ByVal sText As String) As String
    Dim N As Long
    If Len(sText) = 0 Then Exit Function
    
    For N = Len(sText) To 1 Step -1
        Select Case Mid$(sText, N, 1)
            Case "a" To "z", "A" To "Z"
                Exit For
        End Select
    Next N
    Extract = Left$(sText, N)
    ' To get the right half of the string do:
    ' Mid$(sText, N + 1)
End Function

[g4hsean]

I believe bushmobile has the best responce to your question.

[Pradeep1210]

Here's a one liner if you are sure the last character won't be a "0" (zero), otherwise modify appropriately:

Function ExtractLastNum(ByVal sIn As String) as String
    ExtractLastNum = StrReverse(Val(StrReverse(sIn)))
End Function
 
Private Sub Command1_Click()
    ''usage
    MsgBox ExtractLastNum("CV8 7TY0124 121212")
End Sub

[anotherVBnewbie]

If I wanted to parse a string looking for the last number, or letter I'd probably go with a regular expression. RegExps can be more system intensive than other string parsing but they are also more powerfull.

If you wanted to use this approach you would start by adding a reference to MS VBScript Regular Expressions 5.5 and then try this (untested off the top of my head) code:

Dim Reg as New Regular Expression
Dim m as Match
Reg.IgnoreCase
Reg.pattern = "^(.*[a-z])[^a-z]"
for Each m in Reg.Execute(testString)
     ' m.submatches(0) should be the the string through the last letter
next m

If you want to return only the last letter move the parenthesis in the pattern so that the pattern would be "^.*([a-z])[^a-z]"

So you'll understand that a little the reg pattern can be interpeted as saying:

From the start of the string take any number of characters of any type (.*) until a letter is found ([a-z]) which is follwed by anything other than another letter ([^a-z]).

If you want to make it case sensetive remove the ignorecase command and use the appropiately cased a-z. You can even mix and match by using stuff like "[a-zB]" which would match all lower case letters and a uppercase B, etc.

While I'm here, if you are reading files and wan't to parse them a line a time add a "$" to the end of the pattern as the $ is interpetted as "end of line"

[bushmobile]

RegExps can be more system intensive than other string parsing but they are also more powerfull.

They maybe more powerful, but how much power is really needed in a situation like this. It's kinda like trying to hammer a nail with a steamroller - it's just going to slow you down.

Attached is a speed test comparing my code with the RegExp one - whilst they performed equally in the IDE, once compiled the 'built-in' code was 40% faster than the RegExp stuff.

RegExp can be very useful but, for simple things like this, the standard function calls are often faster and of course don't require any overhead.

[schoolbusdriver]

Bush's code still looks best. It's flexible too. Throw a Split in there and you can display the postcode by the phone number. Ideal for telesales...

[szlamany]

I agree with Bushmobile - using a FOR loop and stepping backwards through the string is an age-old standard algorithm for doing something like this.

If you were coding it in ASM you would do exactly the same thing.

I would have checked for 0 through 9 and space myself - but then you never indicated what would happen if a non-letter/non-numeric character was in place - such as a # or @ sign.

[bushmobile]

I would have checked for 0 through 9 and space myself - but then you never indicated what would happen if a non-letter/non-numeric character was in place - such as a # or @ sign.

That's the reason i chose to check for letters

but 0-9 & space would be quicker - also you'd use a byte array if you were looking for more speed

[schoolbusdriver]

At 2Ghz+, bush, who cares! Easily understood + flexible. Nice.

[szlamany]

At 2Ghz+, bush, who cares! Easily understood + flexible. Nice.

Although faster chips are great - speed of CPU is not keeping up with requirement for speed on the part of the user.

Everytime you learn an algorithm, technique or construct and appreciate why it's this-or-that, fast-or-slow, memory-hog-or-not - that makes you a better programmer in the long run.

I've been doing this for a very long time - back to when your program had to fit in 16K of memory.

Every incremental step that CPU, memory and disk size took in the past 20 years was accompanied by a user-thirst for power and speed that surpassed what was just delivered.

Sorry for the rant - but this thread seemed to be all about different ways to attack a simple problem

[smUX]

Sorry for the rant - but this thread seemed to be all about different ways to attack a simple problem

No, don't apologise, you're right to rant...just as long as everyone accepts that as long as each method does the job the poster requested then no-one has given a wrong answer and people giving different possibilities is good for the poster as they have many different methods they can try :-)

I myself started programming on a Commodore +4 which coincidentally only had 16k of memory, so I know where you're coming from...a lot of my coding is about trying to improve speed and do things efficiently...in fact, before learning VB properly about a year ago (I was previously unable to wrap my head around how it all went together, but I eventually managed it) I was writing code in QBX...and I am sure many others here also started or came though a QuickBasic route to get here with VB :-)

[bushmobile]

At 2Ghz+, bush, who cares! Easily understood + flexible. Nice.

You'd care if you had millions of them to process
in this case using a byte array makes it four times faster than using a string.

Everytime you learn an algorithm, technique or construct and appreciate why it's this-or-that, fast-or-slow, memory-hog-or-not - that makes you a better programmer in the long run.

Amen to that

[Merri]

I join in just for the fun of giving competition in the speed market ^_^

Public Function BeforeNumbers(ByRef Text As String) As String
    Dim lngA As Long, lngB As Long
    For lngA = Len(Text) To 1 Step -1
        lngB = AscW(Mid$(Text, lngA, 1))
        If (lngB = 32& Or (lngB >= 48& And lngB <= 57&)) Then Else Exit For
    Next lngA
    If lngA > 1 Then BeforeNumbers = Left$(Text, lngA)
End Function

You can do faster of course, but I still set simplicity, shortness and elegancy over the speed in this one (thus no byte array either) I chose the "check for numbers and spaces" line, but it would only be a matter of either adding more detection for special characters or then just setting the alphabets. It all depends on the case: can there be other alphabets besides the English ones?

Speedwise this code is about two times faster than bushmobile's when compiled. Main reasons being: comparisons stringwise is slow and Select Case is slow.


I jumped to VB from C64. I remember it being weird I couldn't slow down a program by doing For...Next loops

[bushmobile]

i wondered when you'd turn up

this was the version i was talking about in the post before:

Private Function Extract(ByVal sText As String) As String
    Dim N As Long, b() As Byte
    b = sText
    For N = UBound(b) - 1 To LBound(b) Step -2
        If (b(N) > 57 Or b(N) < 48) And Not b(N) = 32 Then Exit For
    Next N
    Extract = Left$(sText, N \ 2 + 1)
    ' To get the right half of the string do:
    ' Mid$(sText, N \ 2 + 2)
End Function

Edit: which would be twice as fast as Merri's code above (compiled)

that's it - i'm stopping there. I'm not going to get into a speed contest cos I know i'd just loose

[Merri]

Well, you already started it

'clsSA.cls
Option Explicit
 
Private Declare Sub RtlMoveMemory Lib "ntdll.dll" (ByRef lpvDest As Any, ByRef lpvSrc As Any, ByVal cbLen As Long)
'Private Declare Function VarPtrArray Lib "msvbvm50.dll" Alias "VarPtr" (Var() As Any) As Long
Private Declare Function VarPtrArray Lib "msvbvm60.dll" Alias "VarPtr" (Var() As Any) As Long
 
Private SA(5) As Long, iarText() As Integer
Public Function BeforeNumbers(ByVal TextStrPtr As Long, ByVal TextLen As Long) As Long
    Dim lngA As Long
    If TextStrPtr = 0 Or TextLen = 0 Then Exit Function
    SA(3) = TextStrPtr: SA(4) = TextLen
    For lngA = TextLen - 1 To 0 Step -1
        If iarText(lngA) < 65 Then Else Exit For
    Next lngA
    If lngA >= 0 Then BeforeNumbers = lngA + 1
End Function
Private Sub Class_Initialize()
    SA(0) = 1: SA(1) = 2
    RtlMoveMemory ByVal VarPtrArray(iarText), VarPtr(SA(0)), 4&
End Sub
Private Sub Class_Terminate()
    RtlMoveMemory ByVal VarPtrArray(iarText), 0&, 4&
End Sub

Private Sub Command1_Click()
    Dim lngA As Long, lngStart As Long, strTest As String, strResult As String
    Dim lngResult1 As Long, lngResult2 As Long
    Dim SA As clsSA
    Set SA = New clsSA
    
    strTest = "CV8 7TY0124 121212"
 
    lngStart = GetTickCount
    For lngA = 1 To 300000
        strResult = Extract3(strTest)
    Next lngA
    lngResult1 = GetTickCount - lngStart
    
    lngStart = GetTickCount
    For lngA = 1 To 300000
        strResult = Left$(strTest, SA.BeforeNumbers(StrPtr(strTest), Len(strTest)))
    Next lngA
    lngResult2 = GetTickCount - lngStart
 
    MsgBox "Extract3: " & lngResult1 & vbNewLine & "SA.BeforeNumbers: " & lngResult2
 
    Set SA = Nothing
End Sub

Life is cruel sometimes Four times faster. This is optimization from the other end compared to my last post: usage becomes harder, but the results speedwise are impressive. This optimization can be counted as "insane" for the task it is done for.

[bushmobile]

that's some lightning fast stuff

you wouldn't mind sticking some comments in the class would you? I've never done much stuff that involves directly moving the memory

[Merri]

'clsSA.cls
Option Explicit
 
Private Declare Sub RtlMoveMemory Lib "ntdll.dll" (ByRef lpvDest As Any, ByRef lpvSrc As Any, ByVal cbLen As Long)
'Private Declare Function VarPtrArray Lib "msvbvm50.dll" Alias "VarPtr" (Var() As Any) As Long
Private Declare Function VarPtrArray Lib "msvbvm60.dll" Alias "VarPtr" (Var() As Any) As Long
 
' SA will be a SAFEARRAY (I used a Long array because it is faster than using UDT)
Private SA(5) As Long
' iarText will have SA as it's header; by default it has no SAFEARRAY header
Private iarText() As Integer
Public Function BeforeNumbers(ByVal TextStrPtr As Long, ByVal TextLen As Long) As Long
    Dim lngA As Long
    ' if pointer is zero or length is zero, then the return value is zero as well
    If TextStrPtr = 0 Or TextLen = 0 Then Exit Function
    ' set where iarText contents are located in memory
    SA(3) = TextStrPtr
    ' set the length of the data
    SA(4) = TextLen
    For lngA = TextLen - 1 To 0 Step -1
        If iarText(lngA) < 65 Then Else Exit For
    Next lngA
    If lngA >= 0 Then BeforeNumbers = lngA + 1
End Function
Private Sub Class_Initialize()
    ' one dimensional
    SA(0) = 1
    ' two bytes per item
    SA(1) = 2
    ' change header used for iarText to our custom SAFEARRAY
    RtlMoveMemory ByVal VarPtrArray(iarText), VarPtr(SA(0)), 4&
    ' it is just a pointer, four bytes (= long value)
End Sub
Private Sub Class_Terminate()
    ' restore original state (iarText had no SAFEARRAY header, thus it is null)
    RtlMoveMemory ByVal VarPtrArray(iarText), 0&, 4&
End Sub

Basically this is based on not moving memory. It avoids it as much as possible.

[bushmobile]

cheers Merri, i think i'll tackle this tomorrow - my brain hurts now

[Pradeep1210]

Anyone tested my Post #9 for speed.

Just for the sake of curosity!

Pradeep

[Merri]

It doesn't do what is requested so there is no interest to do that. But regarding speed, StrReverse is very slow.