Extract 5 digit number from a column

  • Thread starter Thread starter rachlh22
  • Start date Start date
R

rachlh22

I am trying to extract a 5 digit number from a comments field. The comments
field has no formatting, and the 5 digit number may appear anywhere within
the string, here are some examples:

20752-felsen Insurance Services, Denville, Nj **bc
Received Confirm/mailed Approval To Pc 8/3**bc #24166-baily Special Risk

How can I extract this 5 digit number and output it into a column?
 
Your samples show a dash following the 5 digits so if this holds true then
use this --
Right(Left([YourField], InStr([YourField],"-")-1),5)
 
Not all records will have a dash after the number. Those were just examples.
Is there anyway to extract the numbers without having a repeatable pattern
to code against?

KARL DEWEY said:
Your samples show a dash following the 5 digits so if this holds true then
use this --
Right(Left([YourField], InStr([YourField],"-")-1),5)

--
Build a little, test a little.


rachlh22 said:
I am trying to extract a 5 digit number from a comments field. The comments
field has no formatting, and the 5 digit number may appear anywhere within
the string, here are some examples:

20752-felsen Insurance Services, Denville, Nj **bc
Received Confirm/mailed Approval To Pc 8/3**bc #24166-baily Special Risk

How can I extract this 5 digit number and output it into a column?
Click to expand...
Click to expand...
 
If there is only one 5 digit number in the text field, and there is no othe
reasier way to locate this number, a module function could be used as such:

Function Find5(InputStr As String)

Dim i, k As Integer
Dim OutputStr As String

OutputStr = ""
k = 0

For i = 1 To Len(InputStr)
If Mid(InputStr, i, 1) >= "0" And Mid(InputStr, i, 1) <= "9" Then
OutputStr = OutputStr & Mid(InputStr, i, 1)
k = k + 1
Else
If k < 5 Then
k = 0
OutputStr = ""
End If
End If
Next i

Find5 = OutputStr

End Function
 
How do you incorporate functions into Access? I have been building simple
queries using the query builder. Can you give me instructions on how to use
this with a query in the query builder?
 
In the database window, click on Modules. This selects the modules tab. Click
on the New button. Depending on your version of Access (I have 2003) this
opens up a window in which to edit code.

Copy and paste the following into this window:

Function Find5(InputStr As String)

Dim i, k As Integer
Dim OutputStr As String

OutputStr = ""
k = 0

For i = 1 To Len(InputStr)
If k < 5 Then
If Mid(InputStr, i, 1) >= "0" And Mid(InputStr, i, 1) <= "9" Then
OutputStr = OutputStr & Mid(InputStr, i, 1)
k = k + 1
Else
If k < 5 Then
k = 0
OutputStr = ""
End If
End If
End If
Next i

Find5 = OutputStr

End Function


Note: I added an extra If statement to prevent further processing once 5
numbers were found. If your text had "33333 text 45" then it would have
returned inaccurate results. Don't use the function from my previous post.

Click on the Save button and save the module. Module1 works, or call it
something else if you like.

In your query, create a output field "Name: Find5([Text Field])". Replace
"Name" with what you want the field to be called in the header of your output
and "Text Field" with what your text field is called in your input
Table/Query.

This should give you a column with the 5 digit number extracted from your
text field.

Blair
 
Worked like a charm, thank you very much for your assistance.

BlairH said:
In the database window, click on Modules. This selects the modules tab. Click
on the New button. Depending on your version of Access (I have 2003) this
opens up a window in which to edit code.

Copy and paste the following into this window:

Function Find5(InputStr As String)

Dim i, k As Integer
Dim OutputStr As String

OutputStr = ""
k = 0

For i = 1 To Len(InputStr)
If k < 5 Then
If Mid(InputStr, i, 1) >= "0" And Mid(InputStr, i, 1) <= "9" Then
OutputStr = OutputStr & Mid(InputStr, i, 1)
k = k + 1
Else
If k < 5 Then
k = 0
OutputStr = ""
End If
End If
End If
Next i

Find5 = OutputStr

End Function


Note: I added an extra If statement to prevent further processing once 5
numbers were found. If your text had "33333 text 45" then it would have
returned inaccurate results. Don't use the function from my previous post.

Click on the Save button and save the module. Module1 works, or call it
something else if you like.

In your query, create a output field "Name: Find5([Text Field])". Replace
"Name" with what you want the field to be called in the header of your output
and "Text Field" with what your text field is called in your input
Table/Query.

This should give you a column with the 5 digit number extracted from your
text field.

Blair

rachlh22 said:
How do you incorporate functions into Access? I have been building simple
queries using the query builder. Can you give me instructions on how to use
this with a query in the query builder?
Click to expand...
Click to expand...
 
Glad it worked for you.

You can also add another parameter to the function, e.g. knum as Integer,
and where it says "k < 5" use "k < knum". this makes the function more
versatile, that you can pass the number of numeric characters you want. You
would then need to add a constant in your function call, e.g. 5, to specify
the length of the numeric string you want to extract.

Have fun,

Blair
 
Back
Top