In Excel colum how to filter only alphanumeric values? In older version 2010

Question

4554564

Lds7677

87876

Ebt64654

65

Victor

Cbt657557

Raju

565

Edg57566

Ldh878866

7

Ebt867755

6

Arun

Cbt575674

Nani

Answer 1

Create a helper column for example in column B and apply this formula starting from B2 (drag the formula down):

=IF(ISNUMBER(IFERROR(1 / SUM(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9}, IF(A2 = "", "", IF(ISNUMBER(A2), "", A2))), "")), "")), A2, "")

In cell D2 apply below formula as an array formula (so use simultaneously CTRL + SHIFT + ENTER from keyboard):

=IFERROR(INDEX(IF($B$2:$B$25 = "", "", IF(ISNUMBER($B$2:$B$25), "", $B$2:$B$25)), SMALL(IF(IF($B$2:$B$25 = "", "", IF(ISNUMBER($B$2:$B$25), "", $B$2:$B$25)) <> "", ROW($B$2:$B$25)), ROW(D2)) - 1), "")

Drag the formula down.

HTH

Answer 2

Hello,

There's no directly way to do this. You need to introduce a helper column.

Say your data lives in A column starting from A2, where A1 is the header, then B column can be your helper column. In cell B2, you can put in this formula and drag it down. Then you will use this helper column to help you filter. See my screenshot.

=IF(AND(SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1"&":"&LEN(A1))),1),"0123456789"))) > 0,

   SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1"&":"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"))) > 0),

"Alphanumeric", "Numberic")  

Answer 3

I am guessing at your desired result since you didn't show that, and you are wanting to filter out the rows with the numbers and leave the text entries. Here is one way that I believe is possible in the 2010 version though I have no way of actually checking it.

You will need a header for the Filter to work correctly. Here is the procedure.

1. Select all of the data cells in the column. In your example (with a header added) that would be A2:A18.
2. On the Home ribbon open Conditional Formatting>New Rule>Use a Formula. In the Format Values line enter this formula. Note that the row number in the formula must match the first row your selection in step 1.

=ISNUMBER(A2)

3. Click the format button at the lower right and choose the formatting you want in the Format Cells dialogue. I used a light gray in the example so that it wasn't obtrusive. Then click OK and OK.

To filter out the rows that are numerical values, click on the header and apply the Filter on the Data ribbon. Use the filter dropdown for that column, choose Filter by Color, and in the pop-up menu choose No Fill. All of the cells that were colored by the Conditional Formatting will be filtered out.

If this is not your desired result, please reply with more detail and show what you want your desired result to be.

Answer 4

I need result

Lds7677

Ebt6465

cbt657557

Edg57566

Ldh878866

Ebt867755

Cbt575674

Only (result must contain both text and number)

Answer 5

If you need to eliminate the rows with only text also, use the same Conditional Formatting procedure with this formula instead.

=NOT(AND(ISNUMBER(VALUE(LEFT(A2,1)))=FALSE,ISNUMBER(VALUE(RIGHT(A2,1)))=TRUE))

EDIT:

You could also use either of these formulas in a helper column and fill down and then filter the helper column for FALSE.