Find Text and Return

[rhmkrmi]

Hi there,

I am trying to find some texts in a column and then return it for lookup.

For example, in cell A1 I have "Please update the spreadsheet".
I need to extract "update the spreadsheet" in cell B1.
I will then use the value in cell in B1 to do a lookup for labeling.

Can you please help me with the formula?

I used SEARCH and COUNTIFS but they only tell me if the specified text exists in the cell or not. They do not return the text that I want to extract.

Note: I have various values in each cell column A and they are in different positions in the cells which means I cannot easily use LEFT, RIGHT, or MID.
There is little uniformity in the data.

Thank you.

 

[Eric W]

Do you mean this:

Book1
	A	B	C	D	E
1	Please update the spreadsheet	update the spreadsheet			Values to find
2	I want to add a value	add a value			update the spreadsheet
3	Do nothing	#N/A			add a value
4	Insert the name please	insert the name			insert the name
5
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=LOOKUP(8^8,SEARCH($E$2:$E$4,A1),$E$2:$E$4)

 

[rhmkrmi]

Thanks Eric.

I cannot decipher the formula.
Could you please explain what 8^8 does?

I get a result only for the last row:

 

[Eric W]

You're getting the 0s because of the empty cell in E3. If that's a possibility, then we can use a FILTER instead:

Book1
	A	B	C	D	E
1	Please update the spreadsheet	update the spreadsheet			update the spreadsheet
2	I want to add a value	add a value			add a value
3	Do nothing	N/A
4	Insert the name please	insert the name			insert the name
5
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=LET(a,$E$1:$E$4,FILTER(a,(a<>"")*ISNUMBER(SEARCH(a,A1)),"N/A"))

 

[rhmkrmi]

Thank you.

Does the "a" in the formula need to be changed for each cell?
It seems that with "a" in the formula, I cannot simply copy the formula down on the whole column.

 

[Eric W]

The LET function allows you to define temporary variables so that you don't have to repeat intermediate results. I just used the variable name "a". So yes, you can copy it down the column. It might be more understandable if I used an actual name, like "table". It would look like this:

=LET(table,$E$1:$E$4,FILTER(table,(table<>"")*ISNUMBER(SEARCH(table,A1)),"N/A"))

In this case, you don't really need the LET, you can rewrite the formula without it like this:

=FILTER($E$1:$E$4,($E$1:$E$4<>"")*ISNUMBER(SEARCH($E$1:$E$4,A1)),"N/A")

Excel has many ways to do the same thing.

 

[rhmkrmi]

The LET function allows you to define temporary variables so that you don't have to repeat intermediate results. I just used the variable name "a". So yes, you can copy it down the column. It might be more understandable if I used an actual name, like "table". It would look like this:

=LET(table,$E$1:$E$4,FILTER(table,(table<>"")*ISNUMBER(SEARCH(table,A1)),"N/A"))

In this case, you don't really need the LET, you can rewrite the formula without it like this:

=FILTER($E$1:$E$4,($E$1:$E$4<>"")*ISNUMBER(SEARCH($E$1:$E$4,A1)),"N/A")

Excel has many ways to do the same thing.

Thanks a lot for your great help.
It is working although I get the #SPILL if I use a label that is repeated in another cell along with some other words.
I will see if I can find a workaround for it.

 

[Eric W]

Try using TEXTJOIN:

Book1
	A	B	C	D	E
1	Please update the spreadsheet	update			add
2	I want to add a value	add			delete
3	Do nothing	N/A			change
4	Insert the name please	N/A			update
5	add a row, delete the old	add, delete
6	change or update the record	change, update
7
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=TEXTJOIN(", ",1,LET(table,$E$1:$E$4,FILTER(table,(table<>"")*ISNUMBER(SEARCH(table,A1)),"N/A")))

 

[rhmkrmi]

Thanks so much.
It worked!
Well done.

 

[Eric W]

Glad to help!