Query to extract numbers from text string

Posted on 2007-08-08
Last Modified: 2013-11-05
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
Question by:Cotton
    LVL 119

    Expert Comment

    by:Rey Obrero
    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
    LVL 119

    Expert Comment

    by:Rey Obrero
    also you can use the functions  left,mid, right and instr, instrrev depending on the format of the string
    LVL 119

    Expert Comment

    by:Rey Obrero
    post sample data.
    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
                If blNum Then
                    getNumbers = numOnly
                Exit Function
                End If
        End If
    getNumbers = numOnly
    End Function

    to use


    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.



    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now