Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Unanswered: Extract numbers from text

    Hi.
    I have a field in a table containing a text string like this:

    "50402 Motorbikes, 52483 Shop and store, 50403 Maintenance and repair"

    I Neet to extract the three set of 5 digit number into three columns like this:
    "50402" "52483" "50403"
    (I dont care about the text)

    Can anyone please give me a clue how to solve this?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    When I get a challenge like that, I'll usually look to Regular Expressions. Here's a short function that might be in the direction you need. I'm using a module level variable for the object.
    Code:
    Private gre As Object
    
    Public Function GetMyNums(ByVal vstrInString As String) As String
        Dim mc As Object
        Dim m As Object
        Dim strTmp As String
        If (gre Is Nothing) Then
            Set gre = CreateObject("VBScript.RegExp")
            gre.MultiLine = True
            gre.Global = True        
        End If
        gre.Pattern = "\d+"
        ' if there's always 5 digits, then you could do
        ' gre.Pattern = "\d{5}"
        Set mc = gre.Execute(vstrInString)
        For Each m In mc
            strTmp = strTmp & m.Value & ", "
        Next m
        If (Len(strTmp) > 0) Then '
            GetMyNums = Left(strTmp, Len(strTmp) - 2)
        End If
        Set m = Nothing
        Set mc = Nothing
    End Function
    This should work on any PC with IE 5.0 or later installed, and no restrictions on the scripting libraries, and return a string like this

    50402, 52483, 50403

    But, shouldn't you normalize that table of yours
    Roy-Vidar

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Find atach a Text file open it and paste as a module into you msaccess

    make shour there are no red lines

    That The hard bit done
    as we will be using the function in that module

    Words([string]) will count the words in the string

    once we know that

    we get a word out by word([string],number)

    know we test it

    by useing the val() function it will retrun 0 if it has a string in it

    done

    here is the code



    build a New module

    Code:
    Sub test()
    
    Text = "50402 Motorbikes, 52483 Shop and store, 50403 Maintenance and repair"
    
    num = Get_Number(Text, 3)  this will return 50403
    
    
    
    End Sub
    
    
    Function Get_Number(ThisString, ReturnNumber)
    Dim TotalWords
    Dim WordCount As Long
    Dim TestNumber As Variant
    Dim Checkit As Long
    Checkit = 0
    TotalWords = Words(ThisString)            ' count the Words
    
    For WordCount = 1 To TotalWords           ' loop each word in the string
        TestNumber = Word(ThisString, WordCount)  ' get the word
        If Val(TestNumber) > 0 Then               'its a number
            Checkit = Checkit + 1                     'count the number time here
            If Checkit = ReturnNumber Then            'leave have the right one
                Get_Number = Val(TestNumber)
                Exit For
            End If
            If Checkit > ReturnNumber Then      ' bugger no more number in text
                Get_Number = 0
                Exit For
            End If
    
        End If
    Next
    
    
    End Function

    yes you c an use is in a query by

    word1:Get_Number([thisfeild],1)
    word2:Get_Number([thisfeild],2)
    word3:Get_Number([thisfeild],3)
    word4:Get_Number([thisfeild],4)


    sorry about my spellinf that one thing I can't do.
    Attached Files Attached Files
    Last edited by myle; 12-28-05 at 16:03.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Thumbs up 100% solution

    Thanks to both of you.

    I gave myle's proposal a try and it functioned 100% as required.

    Normalizing the tables is next step.

    I'm really pleased to "know" guys like you.
    Thanks again

    Happy new year

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    all i Want is the job know

    Happy new year
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Dec 2003
    Posts
    268

    Another approach

    I would have approached it in a different fashion though here are my thoughts

    Code:
    Public Function NumbersinString(stringtoparse As String) As Collection
        Dim x() As String
        Dim y As Integer
        Dim nums As New Collection
        
        x = Split(stringtoparse)
        For y = 1 To UBound(x)
            If IsNumeric(x(y - 1)) Then nums.Add x(y - 1)
        Next y
        
        Set NumbersinString = nums
        Set nums = Nothing
    End Function
    To reference a particular item in the list you can just reference the index of that object.

    here is an example of how I demonstrated the code

    Code:
    Public Sub testnums()
    Dim x As Collection
    
    Set x = NumbersinString("50402 Motorbikes, 52483 Shop and store, 50403 Maintenance and repair")
    
    Dim y As Integer
    
    For y = 1 To x.Count
        Debug.Print x(y)
    Next y
    
    
    End Sub
    HTH

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    That what I love about scripting

    There is more than one way to skin the cat

    as long as it doesn't [RED]error[\RED] It's right.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •