×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Return Numbers after text value in string
3

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

RE: Return Numbers after text value in string

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

RE: Return Numbers after text value in string

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

RE: Return Numbers after text value in string

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

RE: Return Numbers after text value in string

Given your use case examples, something like:

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

Did any of the above suggestions help?

RE: Return Numbers after text value in string

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close