Return Numbers after text value in string 3

[Moss100]

Hello,

I need to extract in a query the numbers that appear directly after the string "3000_PM216" - so for example it would search the full text [decription] and just return 216. The 300_PM will always be constant.

also I need

I need to extract in a query the numbers that appear directly after the string "Your user ref: PM216" - so for example it would serach the full text [decription] and just return 216. The Your user ref: PM will always be constant.

Many thanks for any help

Mark

 

[dhookom]

Are the numbers always the last three characters of the string?
Do you have a field name to share?
How about some additional examples so we can determine if expressions will work for all values?

Do you understand how to use Instr(), Left(), Right(), and InstrRev()?

What have you already tried?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[hermanlaksko]

You could loop thru the alfanumeric text and if IsNumeric then save the value
Something like this:
Function GetNo(AlfaNum)
Dim I
For I = 1 To Len(AlfaNum)
If IsNumeric(Left(AlfaNum, 1)) Then GetNo = GetNo + Left(AlfaNum, 1)
AlfaNum = Right(AlfaNum, Len(AlfaNum) - 1)
Next
End Function

Herman
Say no to macros

 

[hermanlaksko]

You can add tihe line:

If Len(AlfaNum) > 5 Then AlfaNum = Right(AlfaNum, 5)
in the function if needed or you can expand the function like this:

Function GetNo(AlfaNum, Optional StartFrom as integer)
Dim I
if not StartFrom =0 then
If Len(AlfaNum) > StartFrom Then AlfaNum = Right(AlfaNum, StartFrom )
end if
For I = 1 To Len(AlfaNum)
If IsNumeric(Left(AlfaNum, 1)) Then GetNo = GetNo + Left(AlfaNum, 1)
AlfaNum = Right(AlfaNum, Len(AlfaNum) - 1)
Next
End Function

Herman
Say no to macros

 

[strongm]

Given your use case examples, something like:

[COLOR=blue]Public Function GetRefPM(strText As String) As String
   GetRefPM = Right$(strText, Len(strText) - InStr(strText, "PM") - 1)
End Function[/color]

Or, slightly more generically:

[COLOR=blue]Public Function GetRef(strText As String, Optional delimit As String = "PM") As String
   GetRef = Right$(strText, Len(strText) - InStr(strText, delimit) - (Len(delimit) - 1))
End Function[/color]

 

[strongm]

Did any of the above suggestions help?

 

[Moss100]

Thank you kindly - sorry for my delayed resposnse, I have been a little unwell. Many thanks - Mark