How to extract Alpha characters from a field?

[MsAxes]

Hello all, I have a field [customer_no] that always has a mixture of numbers and letters. I need a field where it pulls the letters from the [customer_no] field. I don't know of an expression that could do that and my searches were of extracting numbers not letters.

Any ideas?

[Bob Fitz]

Can you give a few examples of the [customer_no] data

[MsAxes]

Sure:

Customer_no
2131as5464asyypp1
dg156464st84611dr
1f2dr1gs5498rgsdg0
f5123456sd3154548

There is nothing that would help use the Left, Mid or Right expressions, all number and position is completely unique

[Welshgasman]

You would have to examine each character and add to another variable if alphabetic.

[Gicu]

See this link:
https://stackoverflow.com/questions/...numeric-string

or with a bit of brute force and 10 nested Replace:
Replace(Replace([Cust_No],"0",""),"1","").....

Cheers,

[Clifford]

To extract alpha characters from a field, you can use the SUBSTRING function. The SUBSTRING function extracts a substring from a string.

[June7]

VBA does not have SUBSTRING function.

If there will be only alpha and numbers, no punctuation/symbols, consider:

Function GetAlpha(strA)
Dim strNew As String, x As IntegerFor x = 1 To Len(strA)
    If Not IsNumeric(Mid(strA, x, 1)) Then strNew = strNew & Mid(strA, x, 1)
Next
GetAlpha = strNew
End Function

[Gicu]

The link I provided includes number of versions for a custom VBA function, but if you do not want to rely on any programming you can try the nested Replace in a query.

[Micron]

To extract alpha characters from a field, you can use the SUBSTRING function. The SUBSTRING function extracts a substring from a string.

I'm curious to know what platform allows you to extract only alpha and every alpha character, from any position in a string.

[MsAxes]

All I appreciate your feedback and time. I didn't want to use the replace function so Gicu link and June7 VBA work for what I need.

Thanks again