1

I have a column in excel file which is mix of set of character and numbers, I want to filter out all rows starting with character. Example

Column1

abc
12345
def
5678
xyz
7895
tryc

I want to filter only
abc
def
xyz
tryc

I Have tried using the advance filter by putting ?[a-z], but no luck

2
  • I don't think you can autofilter like that without a helper column. If you don't have any cells with both numbers and letters, try to filter contains: * Or maybe 10 does not contain: number filters. – Raystafarian Feb 11 '14 at 19:17
  • No Rays, No luck – user3223672 Feb 11 '14 at 19:40
1

If you absolutely don't want to use another column, you could try using conditional formatting to help the filtering.

Select cell A1 then the whole column, then use the conditional formatting with 'New Rule' and formula (last option):

=ISTEXT(A1)

Then pick a formatting, for example fill with the colour grey (the colour is up to you, but use fill or a font colour, this this is available in filtering).

Press OK after selecting your formatting and back to your sheet, you can filter on the specific formattings, in this case, on the one you filled with colour or changed font colour (which contains text).

0

The easiest way if you can is to use a secondary column with a formula like:

=ISNUMBER(A2)

Where A2 should be replaced by the appropriate column/row reference.

Then you can easily filter the rows based on whether the cells of that column are true or false.

If you need to, you can always hide the secondary column. Note that if you change your data into a table, adding new rows will keep the formula correct in the hidden column. If you need more flexibility on moving rows around, replace the cell reference with an OFFSET formula. Then, if you cut/paste rows, everything will still be correct.

0

Building upon Raystafarian's comment, you can use the Contains or Does Not Contain values if there are no characters in the string. If there's a chance of a character (e.g. 123c), then it will filter that value out.

To filter against the character v. number in only the first character, you'll need a helper column. Assuming your data in Column A:A, in an adjacent column (e.g. B:B), insert the following formula:

=AND(CODE(LEFT(A1,1))>=48,CODE(LEFT(A1,1))<=57).

This evaluates the first character in your cell (LEFT(A1,1)) and returns it's ASCII code. If the code is between 48 (0) and 57 (9), it will return True, otherwise False. You can then filter off of this column. I agree that you should convert your data into a Table, Excel works and plays well with Table formatted data.

-1

Have a look at this. It gives some interesting examples on advanced filtering. http://searchengineland.com/advanced-filters-excels-amazing-alternative-to-regex-143680

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.