extract only the numeric values in a text 3

[vmauro]

Hi all,

I am tring to extract only the numeric values in a text string in an access qry.

the current field(text)format is as follows

result

6.8 mg
result 9.8
7.2%

i need it to be

clean_result

6.8
9.8
7.2

I search around and found some solutions here but didnt fully understand them, maybe someone could help me out.

thanks

 

[dhookom]

I expect this would take some code. Would your data ever include values like:
7 X 3 oz.
If so, how should this be evaluated.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

As Duane points out there could be a lot of exceptions that would make this difficult. Assuming your data covers the possible cases:

Public Function StripOutNonNumericValues(StringToStrip As Variant) As String
  Dim CurrentPosition As Integer, CurrentCharacter As String, NumericString As String
  If Not IsNull(StringToStrip) Then
     For CurrentPosition = 1 To Len(StringToStrip)
       CurrentCharacter = Mid(StringToStrip, CurrentPosition, 1)
        If IsNumeric(CurrentCharacter) Or CurrentCharacter = "." Then NumericString = NumericString + CurrentCharacter
     Next
    StripOutNonNumericValues = NumericString
 End If
End Function

SELECT tblExample2.Field3, stripOutNonNumericValues([field3]) AS Stripped
FROM tblExample2;

results:

Field3	Stripped
1.2 m/s	       1.2
6.8 mg	       6.8
page 2	       2
123 years      123
10*100         10100
7x3 oz.	       73.

 

[HarleyQuinn]

Just because I like using Regular Expressions (strongm has a lot to answer for! ) here's a way to do this using them (it makes the same presumptions as MajP's post and will return the same results as he posted):

Public Function GetNumericVals(strValue As Variant) As String

Dim re As Object

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .IgnoreCase = True
    .Pattern = "[^\d\.]"
    GetNumericVals = .Replace(strValue, "")
End With

Set re = Nothing

End Function

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

http://lessthandot.com

 

[vmauro]

thanks guys,

most of the data is clean, i have a few HbA1c's that contain %, mg, and result inbedded in the text. i am going to try both to see if i can get it to work. thanks again and i'll post back ASAP with the results

 

[vmauro]

Again, WOW

both worked like a charm

thanks, this was really helpful in cleaning up my lab result qry that i hated cleaning manualy

 

[HarleyQuinn]

Glad we could help, thanks for the star

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

http://lessthandot.com