strRight with 6 and sometimes 7 characters

[ChrisCione]

Hello,

A field in my database is for a control number. The control number varies in length, from 16-20 alphanumeric characters. That control number always* ends in 6 numbers, which I extract and copy to the clipboard using VBA.

*On rare occasion, an alpha character (always an alpha character) may be appended to the end of the number. Example:


"Normal" control numbers

19XYT8C8GGKPPC357395

17JDX8C09YT357395


Modified control numbers

19XYT8C8GGKPPC357395D

17JDX8C09YT357395A


Is it possible to copy the last 7 characters ONLY when an alpha character is appended?

Here's the code currently used:

Dim str As String, strRight As String
On Error GoTo HandleError

str = [txtRPANumber]

strRight = Right(str, 6)
   
Dim clipboard As Object
Set clipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
clipboard.SetText strRight
clipboard.PutInClipboard

HandleExit:
    Exit Sub
HandleError:
    MsgBox "There is no RPA in this record."
    Resume HandleExit

Many thanks in advance.

 

[Jeffrey Mahoney]

Please change the code and replace with this:

Dim Nums As String

  Nums = "0123456789"
  Str = "19XYT8C8GGKPPC357395"
  
  If InStr(Nums, Right(Str, 1)) = 0 Then
    StrRight = Right(Str, 7)
  Else
    StrRight = Right(Str, 6)
  End If

 

[xenou]

also equally good I guess is using IsNumeric() function:

  Str = "19XYT8C8GGKPPC357395"
  If IsNumeric(Right(Str, 1)) Then
    StrRight = Right(Str, 6)
  Else
    StrRight = Right(Str, 7)
  End If

 

[Jeffrey Mahoney]

I still confuse IsNumeric with IsNumber. IsNumber returns FALSE if you have a cell formatted as text with a number in it.

 

[xenou]

It won't matter in Access. There is no IsNumber() formula here (and no cells either, I guess strictly speaking). Good point, though as a reminder for users who work in both Excel and Access applications.

 

[ChrisCione]

Thank you both. I will replace the code tomorrow when I'm back at work.

 

[ChrisCione]

Thanks again to both of you for the help! Works beautifully!