Categorising by certain words

[Yeonsujen]

I have a list of descriptions and need to create another column to categorise them.

This an example of what I want to get for multiple categories and combinations:

Column 1 (Description)	Column 2 (Category)
Year End abcd	Year End
YE abcd	Year End
yEar End abcd	Year End
year End abcd	Year End
Technology abcd	Technology
technology startup abcd	Technology
Tech abcd	Technology

Please note that "abcd" are just an example of random words.

My experience is very limited and I've been using the formula tool to list out every unique description since it seems to be case sensitive and needs to exactly match but with over 1,000 unique cells it's very manual and time consuming. This is what I'm doing:

IF [Column 1 (Description)] = "Year End abcd" THEN "Year End"

ELSEIF [Column 1 (Description)] = "YE abcd" THEN "Year End"

ELSEIF [Column 1 (Description)] = "yEar End abcd" THEN "Year End"

ELSEIF [Column 1 (Description)] = "year End abcd" THEN "Year End"

ELSEIF [Column 1 (Description)] = "Technology abcd" THEN "Technology"

ELSEIF [Column 1 (Description)] = "technology startup abcd" THEN "Technology"

ELSEIF [Column 1 (Description)] = "Tech abcd" THEN "Technology"

ELSE "N/A" ENDIF

Please let me know if there's another easier way to do this! Thank you :)

[DavidSkaife · Accepted solution]

Hi @Yeonsujen 

another option is using a Text Input mapping table and the Find Replace tool. That way you don't need to update the formula to incorporate any changes, simply add the new field to the mapping table and its new category:

[Yeonsujen]

Hi @DavidSkaife, thank you for your reply.

Unfortunately the example data was just a sample, the rest of my data has not yet been categorised so I am not able to use a mapping table for the rest of my data because of the unique descriptions.

I'm looking for a formula where if Column 1 (Description) contains "YE" or "Year end" or "year End" or "YEAREND" then I can categorise this as " Year End". Hope this makes sense, apologies if my initial question is misleading!

[Yeonsujen · Accepted solution]

Found what I was looking for:

IF Contains([Description], "YE") THEN "Year End"
ELSEIF Contains([Description], "Year end") THEN "Year End"

//etc...

ELSE "N/A"
ENDIF

It is not case sensitive either so contains all the uppercase/lowercase descriptions.

[FrederikE]

Hey @Yeonsujen,

You should still use @DavidSkaifes solution. It does the same thing as your Contains function but is easier to maintain and you only need an input table instead of writing the "ELSEIF" statements for every single case. 

F&R does the exact same thing as a contains function when configured Find: "Any Part of the Field".

[Yeonsujen]

Hi @FrederikE, thank you for your reply and explaining it does the same thing - I did try to apply the solution above to my workflow but unfortunately it didn't work despite using the same configurations, including selecting "Any Part of Field". This is definitely a user error on my part!! I wish I could share the results/error but it has too much personal information and I'm not too great with creating a quick mock up, sorry.