Filtering row based on format

[chydewf1]

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

[v-huizhn-msft]

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]

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 · Accepted solution]

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