I can't find an answer to my exact situation here or elsewhere. It's pretty simple, here's an example: ![Excel find text in cell and extract Excel find text in cell and extract](/uploads/1/2/5/2/125287211/599700974.png)
A B
1 bird one
2 bird dog two
3
4 (text to lookup) (VLOOKUP function)![Excel Find Text Within Cell Excel Find Text Within Cell](http://i.stack.imgur.com/JNioq.jpg)
I would like to use a vlookup function in cell B4 to find text in cell A4 within the range A1:B2 and report the value in column B. For example if A4 contains the value 'bird', and I enter in B4 VLOOKUP(A4,A1:B2,2,0) and it returns the value I need - in B1 'one'. But if I enter VLOOKUP('dog',A1:B2,2,0), it returns the #N/A error instead what I need, which is the value in B2 'two'. I need the latter function to report the value in B2 = 'two'. In other words, find the text string WITHIN the cells in column A, not just the matching value. Is there a formula that will get me there or does it have to involve a macro? I much prefer a formula...
![Excel find text in cell and extract Excel find text in cell and extract](/uploads/1/2/5/2/125287211/599700974.png)
Find and extract text between parentheses; Excel FIND function. The FIND function in Excel is used to return the position of a specific character or substring within a text string. The syntax of the Excel Find function is as follows. Jump to Find and extract text between parentheses - Find text between parentheses. Hopefully, this tutorial has shed some light on how to use Search and Find functions in Excel. Formula examples to conditionally sum cells.
(row 1 and col 1 are the spreadsheet row/column)A B
1 bird one
2 bird dog two
3
4 (text to lookup) (VLOOKUP function)
![Excel Find Text Within Cell Excel Find Text Within Cell](http://i.stack.imgur.com/JNioq.jpg)
I would like to use a vlookup function in cell B4 to find text in cell A4 within the range A1:B2 and report the value in column B. For example if A4 contains the value 'bird', and I enter in B4 VLOOKUP(A4,A1:B2,2,0) and it returns the value I need - in B1 'one'. But if I enter VLOOKUP('dog',A1:B2,2,0), it returns the #N/A error instead what I need, which is the value in B2 'two'. I need the latter function to report the value in B2 = 'two'. In other words, find the text string WITHIN the cells in column A, not just the matching value. Is there a formula that will get me there or does it have to involve a macro? I much prefer a formula...
To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. There's no CONTAINS function in Excel.
1. To find the position of a substring in a text string, use the SEARCH function.
Explanation: 'duck' found at position 10, 'donkey' found at position 1, cell A4 does not contain the word 'horse' and 'goat' found at position 12.
2. Add the ISNUMBER function. The ISNUMBER function returns TRUE if a cell contains a number, and FALSE if not.
Explanation: cell A2 contains the word 'duck', cell A3 contains the word 'donkey', cell A4 does not contain the word 'horse' and cell A5 contains the word 'goat'.
3. You can also check if a cell contains specific text, without displaying the substring. Make sure to enclose the substring in double quotation marks.
4. To perform a case-sensitive search, replace the SEARCH function with the FIND function.
Explanation: the formula in cell C3 returns FALSE now. Cell A3 does not contain the word 'donkey' but contains the word 'Donkey'.
5. Add the IF function. The formula below (case-insensitive) returns 'Found' if a cell contains specific text, and 'Not Found' if not.
6. You can also use the IF and the COUNTIF function in Excel to check if a cell contains specific text. However, the COUNTIF function is always case-insensitive.
Explanation: the formula in cell C2 reduces to =IF(COUNTIF(A2,'*duck*'),'Found','Not Found'). An asterisk (*) matches a series of zero or more characters. Visit our page about the COUNTIF function to learn all you need to know about this powerful function.