How do I use a wildcard or "contains" in my if formula?

For example, the cell reads "Smith, Johnny" and I want to write a formulat that returns a result if the cell contains "John". Normally I would use =if(cell,"*John*","YES","NO") with the wildcard * before and after John. But this does not seem to work in SmartSheet. Do I have the syntax incorrect?

Thank you!

Find more posts tagged with

Formulas

Comments

Travis

There isnt a wildcard function but you could get similar results using the FIND function. FIND will till you the place number of a text string in a selected cell.

In your example (Smith, Johnny)...

=FIND("John", [Text2]4)

The result would be 8 ("John" starts at the 8th character. )

Using that, we can add an IF statement to it...

=IF(FIND("John", [Text2]4) > 0, "YES", "NO")

Now, this will show YES if the formula finds "John" in the referenced cell.

Keep in mind, exact spelling and similar capitalization is important. A FIND function looking for "john" will not find "John".

Craig Elliott

Travis,

Since there is no wildcard functionality, how would I use the FIND() function along with COUNTIF? I need to find the number of child rows that contain the text "Contingent". The following combinations should be counted:

Contingent Developer

Contingent

Contingent 11-18-15

However, with my current formula, it will only match on "Contingent": =COUNTIF(CHILDREN(), "Contingent")

Any suggestions?

Travis

FIND() is used to return the starting position of a specific string. With this we can determine if a string exists in a cell, but not a group of cells.

What you can do is, add a Checkbox column with a formula that checks the box if the corresponding cell contains the text string. Then use a COUNTIF to count the number of checkboxes checked.

Cadsolutions - Paul Johnson

Hi I also need a wild card function like that used in excel. I have a sheet which has a Serial No column and a QTY column and I want to capture a total qty of all Serial No's starting with "SFO" for example.

I have used this formula and if I could enter "SFO*" it would solve my problem.

=SUMIF([Serial NO]2:[Serial NO]16, "SFO", QTY2:QTY16)

Excel solves it using =SUMIF(C1:C7,"SFO*",D1:D7)

Hopefully you can suggest a solution to this as obviously we prefer to use Smartsheet over excel

Brad Jones

Could really use a solution to this.

We use one sheet as a schedule, and I need to count up the number of day, afternoon, night, off, shifts for an entire column(employee), but the cell could contain something like "Day Shift: please clean up the office" or "Day Shift: make sure that we order more office supplies". So I really need a count of cells that contain something, not just a countIF one cell at a time.

If I use the proposed solution, every condition I add per column would mean I would need another extra column. So, in my current setup, trying to count the number of cells in a column that contain one of 7 criteria would mean that I would have to add 7 columns for 'verification', when really I only should need the one column.

Anyone find a way around this, other than a nested IF that is 365days*7conditions long?

Mike Blumenstein

You can have the checkbox column use a static referenced cell above (Entry1) to create your formula dynamically, like =IF(FIND($Entry$1, Text@row) > 0, 1, 0) and whatever you enter in the Entry1 cell will populate the row level checkbox formulas on the entire sheet, dynamically.

If you enter John, it will count all of the John, Johnny, Johnson, and such in the column even if it's "Smith, John". You simply have a countif formula =COUNTIF(Entry:Entry, 1) to count the checks and presto, a dynamic formula you can use to search your column and return the match values.

Note: This formula's results are case-sensitive, so it won't pick up john, johnny or johnson in this case.

You can also use a Find and Replace on the Column, which is NOT case-sensitive, to replace all John with John and it will make sure your formulas are correct. (it will actually give you the results too, "Replaced x matches"

I hope this helps!

Find Replace.png

Formula Example.png

AubreyStewart

This is the exact formula I am also trying to use and cannot find a work around without having to add another column. Is there any update or insight available on this?

AubreyStewart

Another user posted this quite some time ago and I don't see a specific answer to this question. I am looking to use the same formula or variation of, in my sheets and do not wish to add additional columns. Thanks!

"Hi I also need a wild card function like that used in excel. I have a sheet which has a Serial No column and a QTY column and I want to capture a total qty of all Serial No's starting with "SFO" for example.

I have used this formula and if I could enter "SFO*" it would solve my problem.

=SUMIF([Serial NO]2:[Serial NO]16, "SFO", QTY2:QTY16)

Excel solves it using =SUMIF(C1:C7,"SFO*",D1:D7)

Hopefully you can suggest a solution to this as obviously we prefer to use Smartsheet over excel."

Marc-Andre Levasseur

Any update on that subject? Any plan on implementing the wildcards in a near future?

I need to make a VLOOKUP for a list of numbers in another sheet where the numbers are not alone in their cell.

e.g. COL_A of SHEET_A lists all numbers. COL_B makes a VLOOKUP into SHEET_B, COL_A where the content looks like this : PROJECT_NUMBER - PROJECT_NAME - PROJECT_DESC (all in one cell).

Having the possibility to make a VLOOKUP with a wildcard would solve my problem : =VLOOKUP([COL_A]1 + "*", {SHEET_B reference}, 6, false)

Thank you!

Craig Elliott

Paul Newcome has the solution HERE (and copied below). Thanks Paul!

Try this...

=COUNTIFS(ActionDue1:ActionDue184, FIND("ids", LOWER(@cell)) > 0)

This will read the entire cell (after making all text lower case to cover if it shows up in upper case) and will give a number based on where in the cell "ids" is found. The number itself is unimportant other than the fact that if "ids" is NOT found, it will return 0. Therefore if it does find it, the number would have to be 1 (first position in the cell) or greater, thus the > 0 portion for the criteria statement.