Reply
Member
Posts: 273
Registered: ‎10-19-2016
Accepted Solution

Switch function ?

I have a colum with different values but i want to make a new calculated column that checks the values in the other column

i believe the switch function would work here but im not sure how to use it.

 

I need a few checks in the formula.

 

He needs to check a couple of things.

 

1. IF value in column 'AP gebied naam' contains 'VB_' then "Vraagbundeling"

else

2. IF value in column 'AP gebied naam' contains '-E0' then "Nieuwbouw"

else

3. "Hoofdproject"

 

Hopefully someone can help me out here


Accepted Solutions
Established Member
Posts: 240
Registered: ‎11-15-2017

Re: Switch function ?

General Switch Formula would be:

SWITCH(['AP gebied naam'];
"VB_";"Vraagbundeling"
"-E0";Niewbouw;
"Hoofdproject")

There is also a CONTAINS formula. But I have never used it before. So if you can give the formula the whole name of the phrase to check this should work.

View solution in original post


All Replies
Established Member
Posts: 240
Registered: ‎11-15-2017

Re: Switch function ?

General Switch Formula would be:

SWITCH(['AP gebied naam'];
"VB_";"Vraagbundeling"
"-E0";Niewbouw;
"Hoofdproject")

There is also a CONTAINS formula. But I have never used it before. So if you can give the formula the whole name of the phrase to check this should work.

Established Member
Posts: 240
Registered: ‎11-15-2017

Re: Switch function ?

Just for perfection including contains:

 

=SWITCH(TRUE();
NOT(ISERROR(SEARCH("VB_";['AP gebied naam'])));"Vraagbundeling";
NOT(ISERROR(SEARCH("-E0";['AP gebied naam'])));"Nieuwbouw";
"Hoofproject")

Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Switch function ?

@FloriankxCONTAINS will not help here, but your switch statement is correct and it's exactly what @RvdHeijden needs here. CONTAINS checks to see if a certain column has a certain value anywhere in the entire column. So it's not particularly useful on a row-by-row case like this one.

Member
Posts: 273
Registered: ‎10-19-2016

Re: Switch function ?

Will the switch function check part of thr text ? Thr VB_ is part of a value and so is the -E0 so will it still work ?
Established Member
Posts: 240
Registered: ‎11-15-2017

Re: Switch function ?

Hello,

 

it should. Please try it and let me know.

 

If more than one LogicalTest is true I think switch stops after the first true one.

 

Best regards.

Highlighted
Super User
Posts: 1,183
Registered: ‎12-29-2015

Re: Switch function ?

No, it doesn't check substrings. The value has to literally be "VB_" or "-E0". If it's something like "VB_-E0" that won't count.