Find Text and Return

rhmkrmi

Active Member
Joined
Aug 17, 2012
Messages
345
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Thanks Eric.

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

I get a result only for the last row:

1730771376476.png
 
Upvote 0
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
ABCDE
1Please update the spreadsheetupdate the spreadsheetupdate the spreadsheet
2I want to add a valueadd a valueadd a value
3Do nothingN/A
4Insert the name pleaseinsert the nameinsert the name
5
Sheet2
Cell Formulas
RangeFormula
B1:B4B1=LET(a,$E$1:$E$4,FILTER(a,(a<>"")*ISNUMBER(SEARCH(a,A1)),"N/A"))
 
Upvote 0
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.
 
Upvote 0
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:

Excel Formula:
=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:

Excel Formula:
=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.
 
Upvote 0
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:

Excel Formula:
=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:

Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top