Extracting Numeric data only from a text field

[CarrieR]

Field name is serial Number, data type is text. Problem is some have numeric value only, some numeric combined with text. How would I extract only numeric values in a query? Text is in the field in various places, 1st, middle of 9 digits, etc.

 

[SkipVought]

Hi

You'll need to write a function

function Digits(sVal as string) 
  for i = 1 to len(sVal)
    b = mid(sVal, i, 1)
    select case b
      case "0" to "9"
        Digits = Digits & b
    end select
  next
end function

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[PHV]

Can you please post some serial Number values and expected result ?
Here a brute force method.
Copy'n'paste this code in a standard code module:

Public Function getNum(myField)
  Dim s As String, l As Long, x As String
  If Len(Trim(Nz(myField, ""))) = 0 Then
    getNum = myField
    Exit Function
  End If
  For l = 1 To Len(myField)
    x = Mid(myField, l, 1)
    If (x >= "0" And x <= "9") Then
      s = s & x
    End If
  Next l
  getNum = s
End Function

Then in the query grid:
NumOnly: getNum([serial number field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CarrieR]

PHV:

Here are some examples of numbers I don't want: 5397M, 4287M, 64554M000123. The ones I do want, from the same field would be as such: 992041234, 042134587. The actual field name is: Modem Serial #.

 

[SkipVought]

function JustNumbers(rng)
  if isnumeric(rng.value) then
    JustNumbers = rng.value
  end if
end function

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CarrieR]

Skip- This did not work.

Thx

Carrie

 

[PHV]

What about something like this ?
Public Function getNum(myField)
If Len(Trim(Nz(myField, ""))) > 0 _
And IsNumeric(Trim(myField)) Then
getNum = Val(myField)
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CarrieR]

Guys

I hate to sound like a real blank wall, but I cannot get this to work. I am not real keen on VB, have tried to place this in a blank module and call this, but I am doing something wrong somewhere.

And thank you all for your help, some day it would be nice if I could do this on my own.

 

[SkipVought]

How are you using this Function?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[PHV]

I cannot get this to work
Any error message ?
The function must be defined in a standard code module, not a form/report module.
The module must have different name than any function/sub.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CarrieR]

Did all as requested, named module different than the function. Called out the function in a query, not getting any results.

 

[SkipVought]

How are you USING this function????????

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CarrieR]

JustNumbers: ([ModemSerial#]) in the query grid (for your example)

 

[lespaul]

probably should be:

JustNumbers(ModemSerial#)

Leslie

 

[PHV]

Then in the query grid:
NumOnly: getNum([serial number field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

[tt]
JustNumbers([ModemSerial#])
[/tt]
NO COLON

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CarrieR]

Skip

Run time error, object required and it locks me out. When I use the function suggested by Skip, I get partial data, for example, when running the query, it will not show the preceding "0" of a series of numbers. It will also, in the field where a number/letter combo used to be, the #error message.

 

[CarrieR]

Skip

Run time error, object required and it locks me out. When I use the function suggested by PHV, I get partial data, for example, when running the query, it will not show the preceding "0" of a series of numbers. It will also, in the field where a number/letter combo used to be, the #error message.

 

[SkipVought]

If PHV's functio seems to work, use this modification

Public Function getNum(myField)
  If Len(Trim(Nz(myField, ""))) > 0 _
  And IsNumeric(Trim(myField)) Then
    getNum = myField
  Else
     getNum = ""
  End If
End Function

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CarrieR]

Same errors as mentioned previously, strips out preceding zeroes and #Error on lines where number/text was.