Need excel function to filter alpha characters

I have a column in excel file which is mix of set alphanumeric,number and alpha character , I want to filter out alpha character only. Need advise..

Column 1

100054
Mk1568
Un10008
Dngh
34677
Gopro

4 Answers

You can use the Advanced Filter with a Formula for a criteria

The formula must return TRUE or FALSE. This returns TRUE if there are no digits within the word:

Formula: =MIN(FIND({0;1;2;3;4;5;6;7;8;9},A6&"0123456789"))>LEN(A6)

Setup

Results

You could also use a simple filter:

• Enter the above formula in B6
• Fill down as far as needed
• Filter on the Contents = TRUE

A simple way is to use =ISTEXT(somevalue) in a new column and filter for true.
You can do the same with =ISNUMBER, =ISBLANK, etc.

Example:

You can use this formula, it will result with the alpha character only and return empty for alphanumeric and numeric cells:

=IF(MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1,1),""))>0,"",A1)
press Ctrl + Shift + Enter Array Formula

It will return the minimum place(first number in the string) for any number in the string if the min >0 (number found), return empty "", if no numeric found return the string (alpha character)
A1 is the initial cell of your Data
You can drag the formula down to test each string, no modification needed

This is a VBA function. To use the function to filter a String, enter =ExtractLetters (string to extract).

Function ExtactLetters(strText as string)
Dim x as integer, strTemp as String
For x = 1 to Len(strText)
IfNotisNumeric(mid(strText, x, 1)) Then
strTemp = strTemp & mid(strText, x, 1)
End if
Next x
ExtractLetters = srtTemp
End Function