Extract numbers from text (1 Viewer)

uatkd1

New member
Local time
Today, 10:27
Joined
Dec 19, 2011
Messages
8
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

Wino Moderator
Staff member
Local time
Today, 08:27
Joined
Aug 30, 2003
Messages
36,118
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

AWF VIP
Local time
Today, 10:27
Joined
Jun 2, 2001
Messages
2,734
Hi -

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

Code:
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

AWF VIP
Local time
Today, 15:27
Joined
Jan 22, 2010
Messages
26,374
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.
Code:
Val(Mid("Minneapolis, MN (21433)", Instr(1, "Minneapolis, MN (21433)", "(") + 1))
 

uatkd1

New member
Local time
Today, 10:27
Joined
Dec 19, 2011
Messages
8
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

AWF VIP
Local time
Today, 11:27
Joined
May 2, 2008
Messages
3,428
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

AWF VIP
Local time
Today, 15:27
Joined
Jan 22, 2010
Messages
26,374
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

New member
Local time
Today, 10:27
Joined
Dec 19, 2011
Messages
8
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

AWF VIP
Local time
Today, 15:27
Joined
Jan 22, 2010
Messages
26,374
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

New member
Local time
Today, 10:27
Joined
Oct 7, 2013
Messages
1
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

New member
Local time
Today, 10:27
Joined
Dec 13, 2013
Messages
2
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:
Code:
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
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:27
Joined
Jul 9, 2003
Messages
16,244
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

Super Moderator
Staff member
Local time
Today, 15:27
Joined
Sep 12, 2006
Messages
15,613
mystring: ALBANY, NY (31534).

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

Solo712

Registered User.
Local time
Today, 11:27
Joined
Oct 19, 2012
Messages
828
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

Retired
Local time
Today, 15:27
Joined
Jun 2, 2003
Messages
12,701
VbaInet gave that formula in post#4 , why did it need repeating?
 

Booker-T

New member
Local time
Today, 08:27
Joined
Aug 4, 2015
Messages
5
Where in the module code do you declare the table / field that you want to extract the data from?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:27
Joined
Aug 30, 2003
Messages
36,118
What code exactly? The function earlier you passed the value to it.
 

GT 11

New member
Local time
Today, 20:57
Joined
May 13, 2019
Messages
4
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:

Code:
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

Enthusiastic Amateur
Local time
Today, 15:27
Joined
Sep 21, 2011
Messages
14,044
If you are after a result of 1234, try replacing the decimal point with something else, perhaps a colon?

Code:
strHold = Replace(Trim(pStr), ".", ":")
? SaveNumer2("Inv.1234")
1234

HTH
 

GT 11

New member
Local time
Today, 20:57
Joined
May 13, 2019
Messages
4
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]
 

Users who are viewing this thread

Top Bottom