Forum Discussion
Re-categorize a column with another column based on partial text
I am trying to make a new text column based on part of the text in another text column. This is to accomplish segmenting the many different but related text fields into a manageable set of categories. It should be as follows;
Description 1 | New Column
FOOD BASICS#949 | GROCERIES
LOBLAWS#876 | GROCERIES
TAXI Drive | Travel
BUS K to B | Travel
METRO#152 PLACE | GROCERIES
What is the best way for me to do this?
Thanks,
Cliff
I appreciate your response, but I think I found a better way. Using DAX I created a CONTAINSSTRINGEXACT function within an IF Function. It worked perfectly!
The function is as follows, with 2 categories;Column = IF(CONTAINSSTRINGEXACT('Sheet1'[Description 1],"METRO"),"Groceries",IF(CONTAINSSTRINGEXACT('Sheet1'[Description 1],"TAXI"),"Travel", "NULL"))It detected the string I wanted it to detect, even if it was within another string.Very cool.
2 Replies
- Ashish_Mathur
Super User
Hi,
You will have to create a 2 column dataset with keywords in the first column and Categories in the next column. So for example, in column 1 you will have Food, LOB and in the next one you will have Groceries against both those entries. We will then search for the keywords in text that appears in every cell of the Description1 column and then place the categories in the second column.
I appreciate your response, but I think I found a better way. Using DAX I created a CONTAINSSTRINGEXACT function within an IF Function. It worked perfectly!
The function is as follows, with 2 categories;Column = IF(CONTAINSSTRINGEXACT('Sheet1'[Description 1],"METRO"),"Groceries",IF(CONTAINSSTRINGEXACT('Sheet1'[Description 1],"TAXI"),"Travel", "NULL"))It detected the string I wanted it to detect, even if it was within another string.Very cool.
