Wildcards in Conditional Formulae

I'd like to create a column of values based on whether a certain string appears in another field.

In the example below I'd like to return a value of 'SEVENTH' if the value in the code column contains '_7', 'FIRST' if it contains '_1' and 'SECOND' if it contains '_2'

code	NEW COLUMN
3117_CORE_7099	SEVENTH
3007_CORE_1099	FIRST
1457_COMP_2099	SECOND

I'm not make any progress with this at the moment. Can anyone help?

Thank you in advance.

Best,

DHB

Preparation

Accepted answers

NicoleJohnson

There is definitely more than one way to solve this problem, and the formula approach will certainly work!

There's another formula option that might be a bit cleaner looking, assuming that the course code always follows the same format of ####_AAAA_#### (or if it doesn't, there are ways around that too...):

Switch(Substring([Course_Code],10,1),"N/A","1","First","2","Second","3","Third","4","Hons/Fourth","5","Hons/Fifth","6","Hons/Sixth","7","Masters")

The Switch formula basically gives you a simpler Case statement than a whole bunch of nested IF statements. At any rate, there are probably a dozen different ways to skin this cat! Glad you were able to find a solution that worked!

All comments

NicoleJohnson

I believe your best bet in this case would be to isolate just that first digit after the second underscore (I used RegEx in the attached example, but there are other string functions you could use in a formula tool such as Substring, or you could use Text to Columns tool based on underscore delimiter and then extract just the first number from the third column created, etc.). Once that number is isolated, you can use a Find & Replace tool to match the number to a set table that shows 1 = FIRST, 2 = SECOND, etc. See attached for an example using the sample data provided.

Does that achieve what you're looking for? Would there ever be a case where it would need to go higher than NINTH?

Cheers,

WildcardFormula.yxmd

DHB

Thank you Nicole,

I've had a bit of a breakthrough here with this formula...

IF Contains([COURSE_CODE],"_1") THEN "First" elseif Contains([COURSE_CODE],"_2") THEN "Second" elseif Contains([COURSE_CODE],"_3") THEN "Third" elseif Contains([COURSE_CODE],"_4") THEN "Hons/Fourth" elseif Contains([COURSE_CODE],"_5") THEN "Hons/Fifth" elseif Contains([COURSE_CODE],"_6") THEN "Hons/Sixth" elseif Contains([COURSE_CODE],"_7") THEN "Masters" else null() endif

I hadn't seen the IF Contains before but it seems to work.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703