Count cells starting with text or containing text

[Anonymous]

Hello

How can I count cells in the matching below?

X*

*Y*Z*

i.e. cells that start with X and cells that contain both Y and Z in that order (first Y then Z).

The * is zero or any character

Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a calculated column with using SEARCH function to to achieve it:

Count = 
var _searchX= SEARCH("X",'Text'[Name],1,0)
var _searchY= SEARCH("Y",'Text'[Name],1,0)
var _searchZ= SEARCH("Z",'Text'[Name],1,0)
RETURN IF(_searchX=1||(_searchY>1&&_searchZ>1&&_searchY<_searchZ),1,0)

Best Regards

Rena

[amitchandak]

@Anonymous ,

Try a new column like

Column =
var _1 =SEARCH("Y",[Column],1,0)
var _2 =SEARCH("Z",[Column],1,0)

return
if(left([Column],1) = "X" && _1 >0 and _2 >_1,1,0)

[DouweMeer]

Something with find expression and switch perhaps? Where you put some logic on whether you find an x and whether the found location comes earlier than the found location of y. And if y is not found, than there is only x.