Text.Contains but whole word, not part of word

[ilcaa722]

I am doing a Text.Contains  for the word ace. it find ACE HARD, SKYES ACE which is good.... BUT it also finds matches that contain Palace which is bad

whats the best way to test a column contains a specific whole word and not part of a word?  thanks

(the text column has multiple words and this is just a short sample)

[ilcaa722]

thanks, i tried ace with variations of spaces, but sometimes its first word,ect... also "ace " picks up Palace Ave

ill try Alexis solution and see if its robust acrross many examples.

[Anonymous]

True, @AlexisOlson, let's make it " ace" OR "ace ". After all, a space after ace finds that case.

[AlexisOlson]

Then you'd get true for both "Palace XYZ" and "XYZ Acetone".

[Anonymous]

In fact, "A space before ace if you do not need space."

[Anonymous]

"A space before ace gives palace no place."

[Anonymous]

Just look for " ace".

--Nate

[AlexisOlson]

@Anonymous  Then you don't get "ACE HARD" but you would get "XYZ ACETONE", right?

[AlexisOlson · Accepted solution]

You could split the text into a list and check if the list contains "ace".

Try this as a custom column:

List.Contains(Text.Split(Text.Lower([Column1]), " "), "ace")

[HotChilli]

spaceacespace?

Couldn't resist that, sorry.