I'm trying to put a formula together that is going to look at my server data and then show what is an internal cloud server.  Started by using the If function

=if(name="PaaS","Yes","No")

but have found that the "name" field doesn't includes just PaaS, it' also has PaaS Runtime, PaaS Lab etc. but all entires do mention PaaS

What I'm trying to get to is if name=(contains the word PaaS,"Internal Cloud","No")

Thought I could use the IN function but that's not working, or I'm not using it correctly

Anyone got any ideas

HI Claire,

Try this...

=if(name="*PaaS*","Yes","No")

As far as I understand the '*' character acts as a wild card, thus *PaaS* will look for PaaS within any surrounding characters.

Chris

Hi, did you try to use the "*" as a wild card in a formula?  Did it work?

Hi Claire,

you could also use the Search() function like this:

=If(Search("PaaS",'Column Name')>0,"Internal Cloud","No")

Search()

This is a great idea, i didn't even think about using the search function as a "Contains"!!

Thomas - worked perfectly - thanks :-)

I use Search all the time for things like this.  You all should bookmark this one.  It comes in handy all the time.

This is awesome! Thank you for this!

Hi Thomas,

Great idea, I used it as well and everything works just fine.

Thank you!

I would consider using the following method:

Transform the table and group by name (if this is not a unique row for each line, if it is this won’t matter) and filter on name “Contains” SaaS. Then do a lookup from your primary table to this new one along the lines [=lookup(name,new table,name,(any column). Then a second column …

If there is a more efficient way I would love to know, but this is the only way I can think of to ensure no future maintenance.

Not sure if the right function supports this or not but my first thought would be to try something like if(right(name,4)= "PaaS"...

Create a tablematch table (PaaS Tablematch) with 2 columns

Old Name     Name

*PaaS*          PaaS

Create a formula for Name=Tablematch(PaaS Tablematch,Name)

Than apply your filter.