Numbers Only

[mlr0911]

Is there a way to write a query where it will only capture numbers? For example, if I have the following:

"Testing transactions 11100202020 in the following order"

What I would like to do is capture the numbers that is in the middle of the sentence. I have tried using the len, left, mid, right functions in my expressions, but the sentence length may change from time to time. Is ther a way to tell access to only capture the numbers?

Thanks in advance
mlr0911

 

[Jon K]

Since the length of the sentence is not fixed, you will have to use VBA.

See the query and the getNum() function in the module in the attached database.
.

 

[mlr0911]

That's Great!!!!

Thanks for your help.

 

[RuralGuy]

That is much more than great! It is elegant.

 

[mlr0911]

1 more thing, how can I modify the code to pick up my leading zero's? I have some numbers that begin with a "0" and need to capture that as well.

This code is wonderful!!!!!!! Thank you so much for your help.

 

[RuralGuy]

Are the numbers always the same number of digits?

 

[mlr0911]

No, some contain 10 numbers, other 13, etc. but no less than 8 and no more than 13.

Thanks

 

[RuralGuy]

I hope Jon does not mind but I made a change so the function will return the string you wanted.

 

[RuralGuy]

I just replaced the uploaded db on my previous post because it had a problem that I didn't catch initially.

 

[CraigDolphin]

Wow. My head hurts interpreting how that short piece of code works. Way to go both of you.

 

[gemma-the-husky]

neat that RG

i like the way it slices off words at a time, so it ignores numbers embedded in strings

however just checking it, theres still a slight catch

a word beginining with a number eg 12items with no spaces returns the whole string 12items in the function result, because val 12items would return 12 and stop the loop

 

[RuralGuy]

Good point Gemma but it may still satisfy the OP's requirement.

 

[Jon K]

The following modification should be able to pick up the number 12 from 12items (as well as retain leading zeros, if any).

Public Function getNum(Mixed As String) As String
   Dim Num As Double
   
   Num = Val(Mixed)
   Do While Num = 0
     If InStr(Mixed, " ") Then
       Mixed = Mid(Mixed, InStr(Mixed, " ") + 1)
       Num = Val(Mixed)
     End If
   Loop

   [b]getNum = Trim(Left(Mixed, InStr(Mixed, Num) + Len(Str(Num)) - 2))[/b]

End Function

.

 

[RuralGuy]

Works like a dream Jon. Excellent.

 

[Moniker]

Keep in mind that searching for "NumbersOnly" would've returned this (not that you knew to search for this, but still ). This will return all the numbers in a string, regardless of where they are, if there are leading zeroes, etc. For example, this string:

ABC 000123 DEF

would return 000123. This string

ABC 000123 DEF45609 43H I

would return 0001234560943. The original reason I wrote this was to remove formatting from phone numbers and SSNs before storing that information in a table. In other words, this string:

(123)456-7890

would return 1234567890, which is how you'd store that information.

Function NumbersOnly(strConvert As String) As String
'Returns only the numbers from a passed string. 

    Dim curChar As String
    Dim ctr As Integer
    
    If IsNull(strConvert) Then
        NumbersOnly = ""
        Exit Function
    End If
    
    For ctr = 1 To Len(strConvert)
        curChar = Mid(strConvert, ctr, 1)
        If IsNumeric(curChar) Then
            NumbersOnly = NumbersOnly & curChar
        End If
    Next

End Function

 

[EMP]

Originally posted by Moniker
The original reason I wrote this was to remove formatting from phone numbers and SSNs before storing that information in a table. In other words, this string:

(123)456-7890

would return 1234567890, which is how you'd store that information.

Not that it would matter much given our computer speeds nowadays, but to remove "(", ")" and "-" from (123)456-7890, you need to iterate only three times instead of 13 times if you use Replace()

^

 

[Moniker]

Very true, but you also get people that put in phone numbers in a lot of different ways:

(123)456-7890
123-456-7890
123/456-7890
123.456.7890

etc., not to mention foreign (non-US/Canada phone numbers), etc. Really, you'd think that some people doing the data entry part of things have zero training on the basics.

 

[mlr0911]

Thanks for all your help.