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
Solved! Go to Solution.
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,
NJ
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.
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!
NJ
That's great, thank you so much Nicole. Much cleaner than my formula