Extract numbers from text

[uatkd1]

Hi Everyone,

I am trying to get just the numbers from a line of text, and here is the original text: ALBANY, NY (31534). The number of digits does not change, but there are many different states in the field (ex: Minneapolis, MN (21433) so the character size will vary.

here is what i have tried so far:

Expr1: Right([textbox7],InStr(1,[textbox7],"("))

Any help would be much appreciated, thank you!

 

[pbaldy]

I would use the Mid() function rather than the Right() function, still using Instr() to find the position of the "(". You'd want to add 1 to that.

 

[raskew]

Hi -

Try copying the following to a module, then call as shown:

Function SaveNumer2(ByVal pStr As String) As Long
'*******************************************
'Purpose:   Removes alpha and non-numeric characters from
'           a string
'Coded by:  raskew
'Calls:     Function IsNumeric()
'Inputs:    ? SaveNumer2("ABC234BB")
'Output:    234
'*******************************************

Dim strHold As String
Dim intLen  As Integer
Dim n       As Integer

    strHold = Trim(pStr)
    intLen = Len(strHold)
    n = 1
    Do
       If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1)) Then
          strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
        n = n - 1
       End If
       n = n + 1
    Loop Until val(strHold) > 0
    SaveNumer2 = val(strHold)

End Function

HTH - Bob

 

[vbaInet]

I would use the Mid() function rather than the Right() function, still using Instr() to find the position of the "(". You'd want to add 1 to that.

Val(Mid("Minneapolis, MN (21433)", Instr(1, "Minneapolis, MN (21433)", "(") + 1))

 

[uatkd1]

I would use the Mid() function rather than the Right() function, still using Instr() to find the position of the "(". You'd want to add 1 to that.

Thank you for the quick response, and the Mid function worked, but I am still getting the last parentheses. I am using MS Access.

Here is the expression I put:
Expr1: Mid([textbox7],InStr([textbox7],"(")+1)

Here is the outcome I am getting:
31534) I want to get rid of that last ")"

Do I need to put another right function? If so can you please give an example.

 

[MSAccessRookie]

The fact that you are getting one too many characters probably indicates that the formula to determine your string is one character too long. Try Mid([textbox7],InStr([textbox7],"(")) instead of Mid([textbox7],InStr([textbox7],"(")+1)

 

[vbaInet]

The solution is starring you in the face in post #4.

The reason is because the Mid() function is returning the rest of the string which includes ")" as the last character. It's either you use the function provided in post #4 or you use the Replace() function to remove the ")" character, or Left() and Left().

 

[uatkd1]

The solution is starring you in the face in post #4.

The reason is because the Mid() function is returning the rest of the string which includes ")" as the last character. It's either you use the function provided in post #4 or you use the Replace() function to remove the ")" character, or Left() and Left().

Thanks,

I used your expression in post 4, but had to put my field name I was using from the table, Textbox7. The expression ended up looking like this: Expr3: Val(Mid([textbox7],InStr(1,[textbox7],"(")+1))

This worked! Thank you so much... this rookie appreciates the help

 

[vbaInet]

but had to put my field name I was using from the table, Textbox7.

That was the whole idea of giving you the expression, so that you substitute the field names in there

Glad we could help!

 

[Sebastiansarrow]

Just a quick "Thank you" to raskew who made post 3 a couple of years ago. Not only did it solve my problem but it was so well documented that it taught me something too!

 

[mcfg]

Hello & good day! First time on here, although I have visited numerous times during learning! I was using raskew's function & found it loops forever if you pass it a string with no numbers. The reason I signed up & am posting this is because its the first result on google & I have adapted it to handle no numbers correctly, hopefully this will help anyone else passing by:

Function GetNumber(ByVal pStr As String) As Long
Dim intLen  As Integer
Dim n       As Integer
    pStr = Trim(pStr) 'removes leading & trending spaces
    intLen = Len(pStr) 'stores original length
    n = 1 'consider this a counter & position marker
    If pStr = "" Or IsNull(pStr) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
    Do
        If IsNumeric(Mid(pStr, n, 1)) Then 'check if that single character is a number
            GetNumber = GetNumber & Mid(pStr, n, 1) 'if it is add to existing ones if any
            n = n + 1 'add to counter so we know to go to next character on the next pass/loop
        Else
            n = n + 1 'it wasn't a number, add to counter so we know to skip it
        End If
    Loop Until intLen = (n - 1) 'go until we processed all characters. The reason we have to do n-1 is that Len starts at 0 & we told n to start at 1
End Function 'if no numbers function will return default value of data type, in our case long would be 0

All credit goes to raskew for that original code I adapted & learned from. I tested mine very quickly & literally have no formal training, so take it with a grain of salt. I want to thank those people who help out of the good of their heart. I (& others) strive on that. Hopefully this helps give back

 

[Uncle Gizmo]

I have adapted the code from this Thread to extract the house number from the 1st line of an address field. The assumption being that if the first character(s) are numbers, then that's the house number. If there is/are NO NUMBER(s) then it's the house name. This is for the HMRC Gift Aid Spreadsheet found HERE:- Gift Aid: schedule spreadsheets to claim back tax on donations The "Extract House Number" code can be found in the code Repository HERE:- Extract House Number

 

[gemma-the-husky]

mystring: ALBANY, NY (31534).

val(mid(mystring,instr(mystring,"(")+1))

 

[Solo712]

mystring: ALBANY, NY (31534).

val(mid(mystring,instr(mystring,"(")+1))

This is by far the best solution ! It deals with the possibility that the zip is nine and not five digits and effectively gets rid of the issue of the closing bracket.

Best,
Jiri

 

[Brianwarnock]

VbaInet gave that formula in post#4 , why did it need repeating?

 

[Booker-T]

Where in the module code do you declare the table / field that you want to extract the data from?

 

[pbaldy]

What code exactly? The function earlier you passed the value to it.

 

[GT 11]

Hi,

This is very useful. Thanks for the solution.
How to tweak this to extract a number from a string that contains a period(.). Example:. "Inv.1234".

Thanks

Hi -

Try copying the following to a module, then call as shown:

Function SaveNumer2(ByVal pStr As String) As Long
'*******************************************
'Purpose:   Removes alpha and non-numeric characters from
'           a string
'Coded by:  raskew
'Calls:     Function IsNumeric()
'Inputs:    ? SaveNumer2("ABC234BB")
'Output:    234
'*******************************************

Dim strHold As String
Dim intLen  As Integer
Dim n       As Integer

    strHold = Trim(pStr)
    intLen = Len(strHold)
    n = 1
    Do
       If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1)) Then
          strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
        n = n - 1
       End If
       n = n + 1
    Loop Until val(strHold) > 0
    SaveNumer2 = val(strHold)

End Function

HTH - Bob

 

[Gasman]

If you are after a result of 1234, try replacing the decimal point with something else, perhaps a colon?

strHold = Replace(Trim(pStr), ".", ":")

? SaveNumer2("Inv.1234")
1234

HTH

 

[GT 11]

It's about extraction of numbers from string .
You had offered a solution in 2011 " Function SaveNumer2(ByVal pStr As String) As Long" but not effective when there is a period in between while extraction of numbers. Example
"Inv.1234". Could you please help.

QUOTEE=pbaldy;1444280]What code exactly? The function earlier you passed the value to it.[/QUOTE]