Solved

# Query to extract numbers from text string

Posted on 2007-08-08
634 Views
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
Question by:Cotton

LVL 119

Expert Comment

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

LVL 119

Expert Comment

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

LVL 119

Expert Comment

post sample data.
0

LVL 119

Accepted Solution

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 Comment

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