## Return Numbers after text value in string

## Return Numbers after text value in string

(OP)

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

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

## RE: Return Numbers after text value in 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

## RE: Return Numbers after text value in string

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

## RE: Return Numbers after text value in string

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

## RE: Return Numbers after text value in string

## CODE -->

`Public Function GetRefPM(strText As String) As String GetRefPM = Right$(strText, Len(strText) - InStr(strText, "PM") - 1) End Function`

Or, slightly more generically:

## CODE -->

`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`

## RE: Return Numbers after text value in string

## RE: Return Numbers after text value in string