Field with mixed text and numbers, need to omit text data

[ragribben]

I have a field that has data with text and numbers. For example:
1/123hg
45/984phl
1/456
53/54656
All data has the / in it and I want to omit only the data with the text in it.
I have tried a number of like and not like type statments but have not been able to figure out how to omit this data.

Thank you for any help you can give.

Becky

 

[DCrake]

Does the text always appear at the rear of the string?
Do you want to retain the /?

 

[ragribben]

Yes I want to retain the / and yes it is always at the rear but the qty of text can vary.

 

[DCrake]

Public Function StripChars(AnyString as string) As String

'/Read the incoming string from right to left 1 character at a time
'/If it comes accross a number it knows to exit the loop
'/It then returns the numeric part of the string.


For x = Len(AnyString) to 1 step -1
   If IsNumeric(Mid(AnyString,x) Then
      Anystring = Left(Anystring,x)
      Exit For
   End If
Next

StripChars = Anystring

End Function

Place this function in a standard module then in your query

AliasName:StripChars([FieldName])

Aircode for brevity only, untested.

 

[ragribben]

Ok, Thank you. I will do this.

 

[philben]

A small correction in above code :

Public Function StripChars(AnyString As String) As String
'/Read the incoming string from right to left 1 character at a time
'/If it comes accross a number it knows to exit the loop
'/It then returns the numeric part of the string.
[B]Dim x As Long[/B]
For x = Len(AnyString) To 1 Step -1
   If IsNumeric(Mid(AnyString, x[COLOR=red][B], 1[/B][/COLOR])) Then
      AnyString = Left(AnyString, x)
      Exit For
   End If
Next
StripChars = AnyString
End Function

Possibly an inline alternative :

Left([MyField], InStr([MyField], "/") - 1) & "/" & Val(Mid([MyField], InStr([MyField], "/") + 1))

Philippe

 

[jdraw]

Here's another version, including a test. I was testing while others submitted apparently.

Sub TestChrs() '45/984phl
Dim s As String
s = "45/984phl"
Debug.Print fKeepNumbers_Slash(s)
End Sub

Function fKeepNumbers_Slash(strIn) As String
'
'keep the slash and any digits
'
Dim I As Integer
Dim strOut As String
   For I = 1 To Len(strIn) Step 1
      If Mid(strIn, I, 1) Like "[0-9/]" Then
         strOut = strOut & Mid(strIn, I, 1)
      End If
   Next I
   fKeepNumbers_Slash = strOut
End Function

 

[stopher]

Here's a solution that doesn't require VBA:

Left([myStr],InStr([myStr],"/")) & Val(Right([myStr],Len([myStr])-InStr([myStr],"/")))

 

[stopher]

A cheekier solution :

Replace(Val(Replace([myStr],"/",".")),".","/")

Edit: assumes numbers never contain the decimal point

 

[stopher]

Possibly an inline alternative :

Left([MyField], InStr([MyField], "/") - 1) & "/" & Val(Mid([MyField], InStr([MyField], "/") + 1))

Philippe

Sorry, I didn't spot this before I posted my first solution.

 

[philben]

Hi,

Sorry, I didn't spot this before I posted my first solution

No problem, we've had the same idea

According to the fastest VBA function, the winner seems to be DCrake.

Regards,

Philippe