Search partial text strings in range and replace with range

[hashbrown91]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Data To Clean[/TD]
[TD]Term to Search[/TD]
[TD]Term To Replace with[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Essential Blue[/TD]
[TD]Blue[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Deep Purple[/TD]
[TD]Purple[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bright Yellow[/TD]
[TD]Yellow[/TD]
[TD]Yellow[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[FormR]

Hi, welcome to the forum!

Something like this maybe:

Excel 2013/2016
	A	B	C	D	E
1	Data To Clean	Term to Search	Term To Replace with
2	Essential Blue	Blue	Blue		Blue
3	Deep Purple	Purple	Purple		Purple
4	Bright Yellow	Yellow	Yellow		Yellow
5	Essential Blue	Red	Something		Blue
6	blueberry				Blue
7	Text Purple Text				Purple
8	Text Red Text				Something
9	text text text				No Match
Sheet1

Cell Formulas
Range	Formula
E2	=IFERROR(LOOKUP(1,-SEARCH($B$2:$B$5,A2),$C$2:$C$5),"No Match")

 

[hashbrown91]

Thank you!

The solution works! Amazing! I had a follow up question though Suppose in A9 there is "blue purple text text text". Both Blue and Purple are in Column B so from my testing the result that will come in E9 will be Blue, since it is the first term in A9 (if Purple was first then Purple would come). Any way to not match the first term that comes in A9 but instead prioritize whichever term in the whole string matches first in Column E? (I hope this makes sense!)

 

[FormR]

Any way to not match the first term that comes in A9 but instead prioritize whichever term in the whole string matches first in Column E?

Hi, the formula as written will return the last found match that is listed in the "Term to Search" column - is this something you can work with to set your priorities?

 

[hashbrown91]

Ah okay, makes sense. Yes I can arrange the "Terms to Search" in a way that works correctly. I was just curious to know if there was a way to expand the formula to account for double matches and give an entirely different result or choose which of the 2 matches would display. Really appreciate the help and knowledge!

 

[FormR]

Glad you can get it working the way you want