Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007
    Posts
    1

    Extract numbers from text string strored in a field.

    Hey guys,

    Anyone know how to create a query or function to extract numbers from a text string? The strings (containing both numbers and texts) are in one field in a table and are variable length. I would like to extract the numbers from the string and return only the numbers portion. The output portion will need to be placed into a different field.

    Example, I have a table called "Furniture". Within this table there are two fields - the first field is called SerialNumber and the second field is called SCode. The first field contains the number and text strings and the second field is blank. I want to be able to run the query or function to extract the number portions from the SerialNumber field into the SCode field. The SerialNumber is variable length; the number and text string is also variable lenght. The total length can be up to 32 characters.

    E.g. 123456ABCDEF
    POUYTR12QQQQTQQQ
    AS987452


    YYYY876346799WWWWWWW

    Please help. Thanks

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you still need it, maybe this function will help:
    Code:
    Public Function ExtractNumber(InString As String) As Double
    
    Do While (Val(InString) = 0) And (Len(InString) > 0)
       InString = Mid(InString, 2)
    Loop
    ExtractNumber = Val(InString)
    
    End Function

  3. #3
    RAPSR is offline Novice
    Windows Vista Access 2000
    Join Date
    Dec 2007
    Location
    El Paso, Texas
    Posts
    6
    Send the subject string, including numeric values, to the ExtractedValue function

    Exam:

    DblNumericValue = ExtractedValue(“123456ABCDEF”) ‘This can be a control value, string value or literal value (for testing)


    I opened a module called Utilities. I then placed the following code in the Utilities module, made it Public so it can be called from any CBF module.

    Public Function ExtractedValue(strSent) As Double

    If IsNull(strSent) Then
    Exit Function
    End If

    Dim strTemp As String
    Dim intLoop As Integer

    'This function extracts a numeric values from a string and returns it
    ' in ExtractedValue

    'Return value of 0 if no numbers found
    ExtractedValue = 0
    strTemp = ""
    'Perform the extraction
    For intLoop = 1 To Len(strSent)
    If Asc(Mid(strSent, intLoop, 1)) >= 48 And Asc(Mid(strSent, intLoop, 1)) <= 59 Then
    strTemp = strTemp & Mid(strSent, intLoop, 1)
    End If
    Next intLoop

    If Len(strTemp) > 0 Then
    ExtractedValue = Val(strTemp)
    End If

    End Function


    I hope this satisfies your needs.

    Richard

    :wink:

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to count charcter or text in field
    By nshaikh in forum Queries
    Replies: 3
    Last Post: 09-12-2008, 10:27 AM
  2. Split text field into two text fields
    By Grant in forum Access
    Replies: 6
    Last Post: 01-31-2008, 05:52 AM
  3. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM
  4. How to extract names from 1 large field
    By Tepin in forum Access
    Replies: 1
    Last Post: 12-04-2006, 01:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums