IF formulas, wildcards

Hi Cherrie,

Thank you for the forum question and the nice feedback.

Wildcards cannot be used in IF.

I am using an IFS function and the ISNUMBER, and SEARCH function. The ISNUMBER function return a TRUE if you have a number and FALSE if you have text.

The SEARCH function finds the position of a text string and return which character number where it can find the text string.

The logic:
ISNUMBER(SEARCH("unpaid",A2))

If SEARCH can find the text string "unpaid" the SEARCH function will return a number and the ISNUMBER function will return TRUE otherwise ISNUMBER will return FALSE.

Here is an example (I have the first text string in A2).

The will return 0 if we have "contract", "unpaid", or "month" in A2, otherwise 1.

=IFS(ISNUMBER(SEARCH("contract",A2)),0,ISNUMBER(SEARCH("unpaid",A2)),0,ISNUMBER(SEARCH("month",A2)),0,TRUE,1)


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector