Filter for rows containing (or not) letters in the values

[jmg80525]

Seems simple, but I'm stumed.  Want to filter for rows that where a letter is present in the cell value.  SQL would be  '[A-Z]. Seems like it would be a common question, but I can't find a solutuion. TIA.

[v-xjiin-msft · Accepted solution]

Hi @jmg80525,

In your scenario, you want to filter the rows which are containing letters. Right?

First you should know there's no such function like SQL which can be used to find the letters directly. Then to achieve this, we need to create a table for all the letters manually. Please refer to my sample, I only put in 3 letters in the letter for test.

Next create a new calculated column in source table with expression like below.

Column 2 =
SUMX ( Letter, IF ( FIND ( Letter[Col], Table2[Val],, 0 ) >= 1, 1, 0 ) )

It means when source table (Table2) exists letters in Letter table, the IF(Find()) returns 1 else 0. Then Sum all the values. It also means, when the expression returns number which is bigger than 0, the row is contaning letters in Letter table. 

Thanks,
Xi Jin.