split text field

[Zukster]

I have a field with codes such as abc1, ab23, mad4 etc.



I want to run a query that will split the field and give me the last # off of each code. Is there a formula to do this?

thanks!

[dcrake]

Will the string always be a block of text followed by a number?

A8CB34 - Never, occasionally, rarely

If you work on the pretext that it may happen what you need is the following function

 
Public Function GrabNumber(AnyString As String) As Integer

'Test for a number as the last character
If IsNumeric(Right(AnyString, 1)) = False Then
   GrabNumber = 0
   Exit Function
End If
For X = 1 To Len(AnyString)

'Read string from right to left until a digit is encountered
 If IsNumeric(Mid(AnyString, Len(AnyString) - X, 1)) = False Then
    GrabNumber = Right(AnyString, X)
    Exit For
 End If
Next
 
End Function

Then in your query

AliasName:GrabNumber([<<YourFieldNameHere>>])

David

[Zukster]

The last field is ALWAYS a number. But sometimes there are other numbers in the field. Can I still use the above code?

[dcrake]

The code I have supplied reads from right to left until it reaches a character that is not a numeric digit, such as any letter of the alphabet. At that point is returns the string of numbers found as an integer.


Code

ABC123 would return 123
AB12C789 would return 789
ABC5D would return 0
123ABC would return 0

David

[expling]

Hello,
I want to split text from a field as follow:

red star becomes "red" -> "star"
red hot chilli becomes "red" -> "hot"; "hot" -> "chilli"
the green house effect becomes "the" -> "green"; "green" -> "house"; "house" -> "effect"

All the symbols like "->; are in the result field.

Anyone know how to do this?
Thanks
exp