Solved

how to extract a number from a string containng text and special character including white space

Posted on 2009-04-12
4
614 Views
Last Modified: 2012-05-06
how to extract a number from a string containng text and special character (like , . / ? etc) including white space
0
Comment
Question by:Rakesh_Prasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 3

Expert Comment

by:SimonThompson
ID: 24125025
Do you know at what position the number is? Is there a particular character that always follows or prefixes the number, give me an example
0
 
LVL 12

Accepted Solution

by:
koutny earned 250 total points
ID: 24125047
You don't say whether the text string has any structure at all, what type of number you are looking for (interger or decimal point), neither where you need to use th extracted number (form, report, query).

However, try the following: Place the code below into a VBA module in your database. Then you can use this function in a query like this:

SELECT other_fields, ExtractFirstNumber(source_field_name) FROM table_name

This somewhat crude function will extract the first number contained within the string. It can be either integer or decimal and can be preceeded by the + or - signs.
Private Function Min(ByVal valueA As Long, ByVal valueB As Long) As Long
    If valueA < valueB Then
        Min = valueA
      Else
        Min = valueB
    End If
End Function
 
Public Function ExtractFirstNumber(ByVal strSourceString As String) As Variant
Dim strNumber As String
Dim l As Long
Dim c As String * 1
Dim i As Long
Dim j As Long
Dim last As Long
    l = Len(strSourceString)
    For i = 1 To l
        c = Mid(strSourceString, i, 1)
        
        If IsNumeric(c) Or ((c = "." Or c = "+" Or c = "-") And IsNumeric(Mid(strSourceString, Min(i + 1, l), 1))) Then
            strNumber = c
            For j = i + 1 To Len(strSourceString)
                c = Mid(strSourceString, j, 1)
                If Not IsNumeric(strNumber & c) Then Exit For
                strNumber = strNumber & c
            Next j
            ExtractFirstNumber = CDec(strNumber)
            Exit Function
        End If
    Next i
End Function

Open in new window

0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 24125105
Rakesh_Prasad,

The UDF below uses Regular Expressions to provide very flexible and powerful text parsing capabilities.

For example, if you want to extract a number from a string like the below:

Th/is col#umn has a 12345 number em?bedded in it plus $pecial char.acters

You would do this:

SELECT SomeColumn, RegExpFind(SomeColumn, "\d+", 1) AS ReturnVal
FROM SomeTable

It would return 12345 as a string.  To return it as a number:

SELECT SomeColumn, Val(RegExpFind(SomeColumn, "\d+", 1)) AS ReturnVal
FROM SomeTable

If your needs are different, then please clarify.

Patrick
Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True) 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Create instance of RegExp object
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
        
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
    
    ' Release object variables
    Set TheMatches = Nothing
    
End Function

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

733 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