Separate text from numbers

[ecohen]

Hi,
It wil be easier if I showing you an example:
I have 1 text field with those values:
@HEMZ00
@TOXP00
@TOXP01
@TOXP02
the other column, I would like to create an expresssion who's giving methis kind OF RESULTS:

@HEMZ
@TOXP
@TOXP
@TOXP

THANKS

 

[Norie]

If the data is as you have posted try the Left function.

NewField:Left([YourField], 5)

 

[ecohen]

Sorry,
I ddin''t mentioned that we could have :
@HEZ00
@TOXYTP0580
@TOWERWXP701
@TEROXP02

 

[Norie]

Is it Access or Excel you require this for?

If it's Excel then take a look at the answers to your post in the Excel forum.

If it's Access another method will need to be found.

 

[ecohen]

Its' access

 

[Norie]

One way could be to write a UDF that finds the first number and returns everything before it.

Perhaps something like this:

Function GetText(strField As String) As String
Dim I As Integer
Dim L As Integer
Dim boolFound As Boolean
Dim strTest As String

    L = Len(strField)
    
    While I <= L And Not boolFound
        I = I + 1
        strTest = Mid(strField, I, 1)
        boolFound = IsNumeric(strTest)
        
    Wend
    
    GetText = Left(strField, I - 1)
    
End Function

 

[ecohen]

Thanks,It's working perfectly!!