Remove Numbers from Field

[Daigriff]

Hi,
I would like to remove the numbers form a field in access e.g.

abcd123 to give abcd
xyz789 to give xyz

Thanks in advance

daigriff

 

[jdraw]

What have you tried?

Here's the essence of a function from Dev Ashish from about10 years ago. It should be a good starting point.

Function fExtractStr(ByVal strInString As String) As String
' From Dev Ashish
'(Q) How do I extract only characters from a string which has both numeric and alphanumeric characters?

'(A) Use the following function. Note that the If loop can be modified to extract either both
' Lower and Upper case character or either
'Lower or Upper case characters.

'************ Code Start **********

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String

    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        'The next statement will extract BOTH Lower and Upper case chars
        If (Asc(strTmp) >= 65 And Asc(strTmp) <= 90) Or _
            (Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then
            'to extract just lower case, use the limit 97 - 122
            'to extract just upper case, use the limit 65 - 90
            strOut = strOut & strTmp
        End If
    Next i
    fExtractStr = strOut
End Function

 

[Brianwarnock]

If your examples cover all scenarios then it amounts to merely removing the last 3 characters

Left(yourstringname,Len(yourstringname)-3)

Brian

 

[Daigriff]

Thanks Brian,
Left(yourstringname,Len(yourstringname)-3) removes the last 3

Right(yourstringname,Len(yourstringname)-3) removes the first 3

Can you tell me how I could join both of these expressions to remove the first and last 3 characters of a string in one go?

Thanks again,
daigriff

 

[Brianwarnock]

Try Mid , starting at the 4th character and a length of string minus 6

Brian