Forum Discussion

Frequent Visitor

8 years ago

Solved

Filtering row based on format

Hi,

I have a list of part numbers where i want to filter anything out that is not in a particular format.

The format I want to keep is:

XX-#####-X-###

Where x = any letter of the alphabet

and # = any number

The sort of thing i want to filter out is:

SKF 618/7

166933

HK0808

etc

Do you think this is possible?

Thanks

Chris

chydewf1
8 years ago
Hi v-huizhn-msft

I think I have solved it - The following seems to work well

= if(mid(Table[Column],11,1)="-"&& mid(Table[Column],9,1)="-"&& mid(Table[Column],3,1)="-",TRUE,FALSE)

Thanks for your help

Chris

3 Replies

v-huizhn-msft
Microsoft Employee
8 years ago
Hi chydewf1,

Please create a column to get ture if text column CONTAINS specified value "-" using search and text.contains in this thread.

I test it using the sample table and create a calculated column through search function.

sign = IF ( IFERROR ( SEARCH ( "-", Test[Value] ), -1 ) > 0, TRUE (), FALSE () )

Then you can use the [sign] column to create a new table, slicer or other filters(page/report level filter) to filter your table rows.

Thanks,
Angelia
- chydewf1
  Frequent Visitor
  8 years ago
  Hi v-huizhn-msft
  
  Thanks for your reply.
  
  It almost works but does not filter this sort of thing:
  0.8X-V175
  
  Ideally I would say if the 3rd, 9th and 11th characters are "-" then return TRUE
  
  Is there any way I can achieve this?
  
  Thanks
  
  Chris
  - chydewf1
    Frequent Visitor
    8 years ago
    Hi v-huizhn-msft
    
    I think I have solved it - The following seems to work well
    
    = if(mid(Table[Column],11,1)="-"&& mid(Table[Column],9,1)="-"&& mid(Table[Column],3,1)="-",TRUE,FALSE)
    
    Thanks for your help
    
    Chris