capture PART OF a text string inside a textbox on a userform

[kbishop94]

basically I cant get the equivalent of the RIGHT formula to work in VBA... Heres what i am trying to do:

I have a textbox on a form that gets populated with the string: "INCIDENT ID#: 18-389" Where the '389' part is increased by 1 each time the form is opened (when opened it checks for the largest value in a column on the sheet and then adds '1'.) So all that works fine... but
When I go to copy the data on the userform to the spreadsheet (this is done when the user selects a commandbutton for "add incident to log" at the bottom of the form)
The code for doing this is:

.Cells(llRow, 1).Value = Me.txtIncidentID.Value

now this all works fine, but I need to exclude the text "INCIDENT ID#: 18-" and just capture the last 3 characters of the textbox which in this example would be "389".

How is this accomplished?? Thank you

Here is a pic of the form when its first opened with the textbox showing the example string "INCIDENT ID#: 18-389"

 

[Rick Rothstein]

Are you saying that this does not work?

.Cells(llRow, 1).Value = Right(Me.txtIncidentID.Value, 3)

If not, what does happen?

 

[lrobbo314]

Not sure if you're ever going to have more than 3 digit numbers to isolate in your string, but if you do, here are a couple of ways to isolate that number without hard coding how many characters you are chopping off at the end.

This is just a little test sub to illustrate how it works.

Sub test()
Dim s As String: s = "INCIDENT ID#: 18-389"
Dim SP() As String
SP = Split(s, "-")
Debug.Print SP(1) 'Using the array
Debug.Print Replace(s, Left(s, InStr(s, "-")), "") 'Using text functions


s = "INCIDENT ID#: 18-1347"
SP = Split(s, "-")
Debug.Print SP(1) 'Using the array
Debug.Print Replace(s, Left(s, InStr(s, "-")), "") 'Using text functions
End Sub

 

[kbishop94]

Are you saying that this does not work?

.Cells(llRow, 1).Value = Right(Me.txtIncidentID.Value, 3)

If not, what does happen?

Yes. that worked. Thank you (I had it backwards and had the "RIGHT" listed after the "Me.txtIncidentID" part )

 

[kbishop94]

Not sure if you're ever going to have more than 3 digit numbers to isolate in your string, but if you do, here are a couple of ways to isolate that number without hard coding how many characters you are chopping off at the end.

This is just a little test sub to illustrate how it works.

Sub test()
Dim s As String: s = "INCIDENT ID#: 18-389"
Dim SP() As String
SP = Split(s, "-")
Debug.Print SP(1) 'Using the array
Debug.Print Replace(s, Left(s, InStr(s, "-")), "") 'Using text functions


s = "INCIDENT ID#: 18-1347"
SP = Split(s, "-")
Debug.Print SP(1) 'Using the array
Debug.Print Replace(s, Left(s, InStr(s, "-")), "") 'Using text functions
End Sub

Thank you! Yes, eventually there will come a point where 4 digits might be a possibility. Yours does the trick to cover that possibility. THANK YOU!