Extract a number out of text

[krester]

I have a text field that includes numbers. How can I extract the numbers to another field by using a query?

 

[plog]

It really depends on what the string looks like and what you are calling a number

Suppose you start with 'AX172839BD63'

How many numbers is that? Is it 2 (172839 & 63) or is it 8 (1,7,2,8,3,9,6,3)? Or is it some other permutation of digits?

Can you give a few examples and the output you expect back?

 

[krester]

Actually, it's an address field.

Here are some examples:

Text
1. 12 windflower st.
2. Rose st. 24/2, flowers village
3. Tulip eve. 125

The expected output
1. 12
2. 24/2
3. 125

Thank you so much for helping me!

 

[plog]

'24/2' isn't a number, at best its an operation that equals 12. Also, what about this:

1768 West 13th Street, Apt. #4

What would you want that to return?

 

[Mr. B]

Here is a user defined function that will return what you want.

Public Function ExcludeText(InputValue As String) As String
Dim varCharCnt
Dim cntr
Dim strChar As String
varCharCnt = Len(InputValue)
For cntr = 1 To varCharCnt
    strChar = Mid(InputValue, cntr, 1)
    If Asc(strChar) <= 65 And Asc(strChar) <> 32 _
       And Asc(strChar) <> 46 And Asc(strChar) <> 44 Then
        ExcludeText = ExcludeText & strChar
    End If
Next cntr
End Function

Copy this code and paste it into a Module. Then you can place the following into the Control Source of another text box:

=ExcludeText([Text4])

Change the "[Text4]" to the name of your control that has the address in it.

If you should find that you need to exclude other characters from the returned value, you can just add more criteria to the "If" statement.

 

[krester]

Is it possible to spread it to 3 different fields?

Well, I see I have a problem….

 

[krester]

Mr. B - thank you so much,
I followed your directions and – its working. I added Or strChar = " " to the if in order to solve the problem plog talked about. Thank you plog as well for divert my attention to the problem
Now - how can I keep the result in another field?

 

[Brianwarnock]

Now - how can I keep the result in another field?

Do you mean another field in the original Table?
If so why? It not only breaks normalization rules but it is not necessary as the data can be extracted as required and will be current.

Brian