6 Replies Latest reply on Jul 12, 2018 3:35 PM by Stephen Hicks

# ISNUMBER

Can someone help me with finding out how to edit this formula to search for an exact match. I only want it to find the word ant. Right now it pulls words like want, wanted, and chalant.

=IF(ISNUMBER(SEARCH("*ant*",K5)),"ant","")

Thank you,

Barb

• ###### 1. Re: ISNUMBER

Hi Barb,

The test data doesnt have "ant" but it has "Ants". for Ants you can use following calculation

CONTAINS([Please describe the problems], 'ants')

Here are the results

• ###### 2. Re: ISNUMBER

Hi Sanjay,

I need it in the same excel friendly format. I have to build out my formulas individually in excel and then I'm going to pivot my formula columns within tableau.

As far as ant vs ants, can we allow for plural values but the start of the word has to match exactly?

Barb

• ###### 3. Re: ISNUMBER

Late to the party. Sorry for the interruption.

--Shawn

• ###### 4. Re: ISNUMBER

in that case, you can use,

=IF(ISNUMBER(SEARCH("ants",K5)),"ant","")

• ###### 5. Re: ISNUMBER

Hi Sanjay, I had tried removing the * but that doesn't change the results.

• ###### 6. Re: ISNUMBER

Here you go!

LEN(REGEXP_REPLACE([Seat From Num],'^[0-9]*\$',''))=0

null = null

False = Alphanumeric

True = Numeric

Works by removing all numbers to see if you end up with nothing left