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

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 :)

Thanks in advance
0
Cotton
Asked:
Cotton
  • 4
1 Solution
 
Rey Obrero (Capricorn1)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
 
Rey Obrero (Capricorn1)Commented:
also you can use the functions  left,mid, right and instr, instrrev depending on the format of the string
0
 
Rey Obrero (Capricorn1)Commented:
post sample data.
0
 
Rey Obrero (Capricorn1)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
 
CottonAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now