Extract numbers from a string

  • Thread starter Thread starter Bill Phillips
  • Start date Start date
B

Bill Phillips

I have a free-form field that may have numbers included in
it. However, there is no pattern to the numbers. It could
be any of the following for example: DVM388, NVM415JJ, CC
231 N or the like. Also, it may not include any numeric
characters at all.

I would like to run whatever code I need to develop in a
make table query but don't know how to call a module from
a query. Also I believe I can work through the code
(basically evaluate each element of the string and
concatenate the numeric elements together using ASCII 48-
57 for numbers) but I need to do it for each recordset. My
query will be evaluating up to 100,000 recordsets.

I saw a function on mvps.org for extracting characters
from a string that I can adapt for my sitation, I just
don't know how or where to call it.

Thanks in advance.
 
Bill Phillips said:
I have a free-form field that may have numbers included in
it. However, there is no pattern to the numbers. It could
be any of the following for example: DVM388, NVM415JJ, CC
231 N or the like. Also, it may not include any numeric
characters at all.

I would like to run whatever code I need to develop in a
make table query but don't know how to call a module from
a query. Also I believe I can work through the code
(basically evaluate each element of the string and
concatenate the numeric elements together using ASCII 48-
57 for numbers) but I need to do it for each recordset. My
query will be evaluating up to 100,000 recordsets.

I saw a function on mvps.org for extracting characters
from a string that I can adapt for my sitation, I just
don't know how or where to call it.
Click to expand...
Hi Bill,

In the Database Window,
select Modules in the Object frame
on the left, then click on New.

When the VBA window comes up,
click on "floppy" icon in top menu
to save, and save as "modUtilities"

All you should have in the code window now is

Option Compare Database

underneath it, type in

Option Explicit

Now, copy and paste the following function
under "Option Explicit"

Public Function fExtractNum(pInString As Variant) As String
Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String
'adapted from http://www.mvps.org/access/strings/str0002.htm
On Error GoTo Err_fExtractNum

If Len(Trim(pInString & "")) > 0 Then
lngLen = Len(pInString)
strOut = ""
For i = 1 To lngLen
strTmp = Left$(pInString, 1)
pInString = Right$(pInString, lngLen - i)
If IsNumeric(strTmp) = True Then
strOut = strOut & strTmp
End If
Next i
fExtractNum = strOut
Else
fExtractNum = vbNullString
End If

Exit_fExtractNum:
Exit Function

Err_fExtractNum:
MsgBox Err.Description
Resume Exit_fExtractNum

End Function


Click on save icon again.

Now click on "Debug" in top menu,
and click on "Compile .."

If you get no errors, you are all set!

You can test the function in the Immediate
window at the bottom of the page. For instance,
type in the following and hit enter

?fextractnum("NVM415JJ")

You should get result in next line below it.

415

Close code window.

To use in query, use function in a calculated field

SELECT *,
fExtractNum([yourfield]) As NumOnly
FROM
yourtable;

(of course, replace "[yourfield]" with
actual name of field in your table that
you are parsing out)

In the query designer, in a Field row
in the grid, you would type something like:

NumOnly: fExtractNum([yourfield])

This function returns a number *string*
plus, if no number in string, returns vbNullString.

So...if you try to use conversion function,
say CLng() on the result to get a number,
you will get Runtime Error 13 for any null strings.

Your data will dictate what you want to do.

For instance, is "0" an option for those strings
that do not contain a number? Then use
the Null-To-Zero function:

CLng(NZ(fExtractNum([yourfield]),"0"))

You could replace "0" with any number though,
say some huge, obvious number that flags
that record as having no number in the
parsed field.

CLng(NZ(fExtractNum([yourfield]),"999999"))


You know your data best.

Please respond back if I have not been
clear about something.

Good luck,

Gary Walter
 
-----Original Message-----
Click to expand...

Gary,

This was exactly what I was looking for. It works
perfectly *right out of the box*. Thanks a million

Bill

I have a free-form field that may have numbers included in
it. However, there is no pattern to the numbers. It could
be any of the following for example: DVM388, NVM415JJ, CC
231 N or the like. Also, it may not include any numeric
characters at all.

I would like to run whatever code I need to develop in a
make table query but don't know how to call a module from
a query. Also I believe I can work through the code
(basically evaluate each element of the string and
concatenate the numeric elements together using ASCII 48-
57 for numbers) but I need to do it for each recordset. My
query will be evaluating up to 100,000 recordsets.

I saw a function on mvps.org for extracting characters
from a string that I can adapt for my sitation, I just
don't know how or where to call it.
Click to expand...
Hi Bill,

In the Database Window,
select Modules in the Object frame
on the left, then click on New.

When the VBA window comes up,
click on "floppy" icon in top menu
to save, and save as "modUtilities"

All you should have in the code window now is

Option Compare Database

underneath it, type in

Option Explicit

Now, copy and paste the following function
under "Option Explicit"

Public Function fExtractNum(pInString As Variant) As String
Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String
'adapted from http://www.mvps.org/access/strings/str0002.htm
On Error GoTo Err_fExtractNum

If Len(Trim(pInString & "")) > 0 Then
lngLen = Len(pInString)
strOut = ""
For i = 1 To lngLen
strTmp = Left$(pInString, 1)
pInString = Right$(pInString, lngLen - i)
If IsNumeric(strTmp) = True Then
strOut = strOut & strTmp
End If
Next i
fExtractNum = strOut
Else
fExtractNum = vbNullString
End If

Exit_fExtractNum:
Exit Function

Err_fExtractNum:
MsgBox Err.Description
Resume Exit_fExtractNum

End Function


Click on save icon again.

Now click on "Debug" in top menu,
and click on "Compile .."

If you get no errors, you are all set!

You can test the function in the Immediate
window at the bottom of the page. For instance,
type in the following and hit enter

?fextractnum("NVM415JJ")

You should get result in next line below it.

415

Close code window.

To use in query, use function in a calculated field

SELECT *,
fExtractNum([yourfield]) As NumOnly
FROM
yourtable;

(of course, replace "[yourfield]" with
actual name of field in your table that
you are parsing out)

In the query designer, in a Field row
in the grid, you would type something like:

NumOnly: fExtractNum([yourfield])

This function returns a number *string*
plus, if no number in string, returns vbNullString.

So...if you try to use conversion function,
say CLng() on the result to get a number,
you will get Runtime Error 13 for any null strings.

Your data will dictate what you want to do.

For instance, is "0" an option for those strings
that do not contain a number? Then use
the Null-To-Zero function:

CLng(NZ(fExtractNum([yourfield]),"0"))

You could replace "0" with any number though,
say some huge, obvious number that flags
that record as having no number in the
parsed field.

CLng(NZ(fExtractNum([yourfield]),"999999"))


You know your data best.

Please respond back if I have not been
clear about something.

Good luck,

Gary Walter





.
Click to expand...
 
Back
Top