• Status: Solved
• Priority: Medium
• Security: Public
• Views: 690

# Query to extract numbers from text string

Hi Experts,

Need a bit of quick help.
I have a  table with [ID], [Date], [ClientID],[reference]

Some of the records have a series of numbers in the [reference] field  eg. 10215, 10555, 10331

I'm want to make a query to create a new table with these numbers extracted with their own record each, but I need help of on the extraction part :)

0
Cotton
• 4
1 Solution

Commented:
where are the numbers located

if the numbers are located in front  12345abj

val("12345abj")  will give you  12345

if anywhere else , you will need a function
0

Commented:
also you can use the functions  left,mid, right and instr, instrrev depending on the format of the string
0

Commented:
post sample data.
0

Commented:
here is a function you can use

Function getNumbers(s As String) As Long
Dim j, numOnly, blNum As Boolean
blNum = False
For j = 1 To Len(s)
If IsNumeric(Mid(s, j, 1)) Then
blNum = True
If blNum Then
numOnly = numOnly & Mid(s, j, 1)
End If
Else
If blNum Then
getNumbers = numOnly
Exit Function
End If
End If
Next
getNumbers = numOnly
End Function

to use

getNumbers([fieldName])
0

Author Commented:
Super quick response.
For clarificaion:
The [Reference] data in each field is generally "12345", but the ones I need to extract are "10155, 10211, 10255" and ignore the records with text in the record.

I'm just going to try out your function.

Cheers

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.