Remove letters from numbers

[Corey]

Hello again.

I’m trying to find a way in a query to remove any data except for numbers

Examples


NET 90
NET 10
NET 10 DAYS
NET 110
NET 15


Any help would be greatly appreciated

Corey

 

[Rickster57]

Query

This depends on the consistency of your data. There is no way to just "Strip" the letters away Assuming it is always something like
10
or
10 DAYS
or
NET 10 DAYS

then use the following 3 expressions in your query. Expr3 will leave only the numbers where [w] is the data base field storing the information.


expr1: Len([w])

expr2: IIf(Left([w],3)="NET",Right([w],[expr1]-4),[w])

expr3: IIf(Right([w],4)="Days",Left([expr2],Len([expr2])-5),[expr2])

Hope this helps

 

[raskew]

Hi -

The following will remove all non-numerical characters from any string:

HTH - Bob

Function SaveNumer(ByVal pStr As String) As String
'*******************************************
'Purpose:   Removes alpha characters from
'           a string
'Coded by:  raskew
'Calls:     Function IsNumeric()
'Inputs:    ? SaveNumer(" t#he *qu^i5ck !b@r#o$w&n fo#x ")
'Output:    5
'Note:      As written, empty spaces are ignored.
'*******************************************

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 Mid(strHold, n, 1) = ""
    SaveNumer = Trim(strHold)
    
End Function

 

[Corey]

Thanks Rickster

 

[raskew]

Golly,

Bob

 

[RuralGuy]

Merry Christmas to you too Bob!