hashbrown91
New Member
- Joined
- Aug 23, 2017
- Messages
- 3
Hi everyone,
I wanted to check if someone knows a way of simplifying a data cleaning process I do often. Suppose below is the data I have to clean:
<tbody>
</tbody>
So far here is the formula I have been using:
=IF(ISNUMBER(SEARCH("Blue",A2)),"Blue",IF(ISNUMBER(SEARCH("Purple",A2)),"Purple",IF(ISNUMBER(SEARCH("Yellow",A2)),"Yellow","No Match") and then I just drag the formula down.
This does the job, but as you can tell it's a very manual process especially with large data sets since all conditions have to be entered individually so there is also a lot of room for error. What I wanted to know is if it is possible to search the entire Range of Column B in Column A, and then replace with the relevant term in Column C? I have tried using ranges in this current formula but it doesn't seem to work.
Any help would be appreciated! Cheers!
I wanted to check if someone knows a way of simplifying a data cleaning process I do often. Suppose below is the data I have to clean:
A | B | C | |
1 | Data To Clean | Term to Search | Term To Replace with |
2 | Essential Blue | Blue | Blue |
3 | Deep Purple | Purple | Purple |
4 | Bright Yellow | Yellow | Yellow |
<tbody>
</tbody>
So far here is the formula I have been using:
=IF(ISNUMBER(SEARCH("Blue",A2)),"Blue",IF(ISNUMBER(SEARCH("Purple",A2)),"Purple",IF(ISNUMBER(SEARCH("Yellow",A2)),"Yellow","No Match") and then I just drag the formula down.
This does the job, but as you can tell it's a very manual process especially with large data sets since all conditions have to be entered individually so there is also a lot of room for error. What I wanted to know is if it is possible to search the entire Range of Column B in Column A, and then replace with the relevant term in Column C? I have tried using ranges in this current formula but it doesn't seem to work.
Any help would be appreciated! Cheers!