If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extract 5 digit number from a column
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? |
#2
|
|||
|
|||
Extract 5 digit number from 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) -- Build a little, test a little. "rachlh22" wrote: 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? |
#3
|
|||
|
|||
Extract 5 digit number from a column
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" wrote: 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" wrote: 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? |
#4
|
|||
|
|||
Extract 5 digit number from a column
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 "rachlh22" wrote: 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? |
#5
|
|||
|
|||
Extract 5 digit number from a column
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? "BlairH" wrote: 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 "rachlh22" wrote: 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? |
#6
|
|||
|
|||
Extract 5 digit number from a column
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" wrote: 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? "BlairH" wrote: 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 "rachlh22" wrote: 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? |
#7
|
|||
|
|||
Extract 5 digit number from a column
Worked like a charm, thank you very much for your assistance.
"BlairH" wrote: 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" wrote: 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? "BlairH" wrote: 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 "rachlh22" wrote: 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? |
#8
|
|||
|
|||
Extract 5 digit number from a column
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 "rachlh22" wrote: Worked like a charm, thank you very much for your assistance. |
Thread Tools | |
Display Modes | |
|
|