0
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
0
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
1
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)
You could also use a simple filter:
TRUE
0
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
-1
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
To use the Function to filter String,,
=ExtractLetters (string to Xtract) ☺ – Rajesh S – 2017-03-27T10:40:35.677
I'm new to the Super User, could you please tell me the reason behind my code is been edited. – Rajesh S – 2017-03-27T15:27:21.130
The code was not formatted. The alternative, to improving the answer, is to delete an unformatted answer. – Ramhound – 2017-04-18T03:14:25.943
3What have you tried? Where did that get you? You've given your input data but what is your expected output? – Mokubai – 2017-03-26T06:54:23.970
I need to filter out only alpha character. outputs-dngh gopro – Manikandan r – 2017-03-26T07:01:47.373
I used below. =ISNUMBER(SUMPRODUCT(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))) – Manikandan r – 2017-03-26T07:06:42.523
1What is the exact issue with your formula? – Máté Juhász – 2017-03-26T07:19:04.450