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

how to extract a number from a string containng text and special character (like , . / ? etc) including white space
###### Who is Participating?

Commented:
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
``````
0

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

Commented:

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

Patrick
``````Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
Optional MatchCase As Boolean = True)
' 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 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)
Next

' 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
``````
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.