Find the First Letter at the Right end of a field. 1

[uncleG]

I have been using the following public function in my query which both PHV and Golom had shared with me back in 2009. Thread 701-1577313

GetBPName = Split(Split(Split(Split(myfield & "", " ")(0), "_")(0), "-")(0), ".")(0)

Function Sample Data Current Result
GetBPName(B15722.pdf ) = B15722
GetBPName(B16728D Outline.pdf ) = B16728D
GetBPName(B15452A Outline 3AO-948.pdf) = B15452A
GetBPName(2343900- Outline 3ZO-996.pdf) = 2343900
GetBPName(A2343900AC- Outline 8RU-696.pdf) = A2343900AC

I would like to modify the above function or call a second function so as to be able to insert the word “ Rev “ between the last number and first letter when a letter or letters follow the numbers and when there is no ending letter or letters then just leave it alone.

Sample… Desired result.
B15722 = B15722
B16728D = B16728 Rev D
B15452A = B15452 Rev A
2343900 = 2343900
A2343900AC = A2343900 Rev AC

Thanks,
uncleG

 

[Andrzejek]

Option Explicit

Private Sub Command1_Click()
    MsgBox AddRev("B16728D")
End Sub
[blue]
Private Function AddRev(strIn As String) As String

strIn = Trim(strIn)
AddRev = strIn

If Not IsNumeric(Right(strIn, 1)) Then
    AddRev = Left(strIn, Len(strIn) - 1) & " Rev " & Right(strIn, 1)
End If

End Function[/blue]

Have fun.

---- Andy

 

[uncleG]

Hi Andrzejek,

Thanks for your rapid reply, I ran your function against my sample record set and it work with all except the last sample.
A2343900AC = A2343900 Rev AC
It returned A2343900 Rev C

How can I handle it when it ends with double letters at the right as some of our revisions my reach that point before I retire?
Thanks,
uncleG

 

[Andrzejek]

Ooops, sorry, missed this one.... :-(

Acually, you get [tt]A2343900A Rev C[/tt], but that's not important.

Try this:

Private Function AddRev(strIn As String) As String
Dim i As Integer

strIn = Trim(strIn)
AddRev = strIn

If Not IsNumeric(Right(strIn, 1)) Then
    For i = Len(strIn) To 1 Step -1
        If IsNumeric(Mid(strIn, i, 1)) Then
            i = Len(strIn) - i
            Exit For
        End If
    Next i
    AddRev = Left(strIn, Len(strIn) - i) & " Rev " & Right(strIn, i)
End If

End Function

Have fun.

---- Andy

 

[uncleG]

Thanks Andy,
I tested the function out to 4 trailing letters.
Now if only I could understand it .

 

[Andrzejek]

It is not that though....
Just put the break point on (for example) line:

[tt]strIn = Trim(strIn)[/tt]

(Place the cursor anywhere on this line and press F9) Then press F5 to run it. When you stop on the line with the break, keep pressing F8 to step thru. You can move your mouse over any variables to see their value. Piece of cake.

It should work OK with anything like ABC123XYZTYSR, but it will be wrong it you have something like ABC123X[red]5[/red]A (number 5 between X and A)

Have fun.

---- Andy