Categorising by certain words

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

Workflow

Help

Preparation

Accepted answers

davidskaife

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:

Find Replace.yxmd

Yeonsujen

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.

All comments

davidskaife

Hi @Yeonsujen

Find Replace.yxmd

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

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".

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335