Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Return Specific Text if Match in Adjacent Cell

How to display or to confirm if there is the certain word in the text located in the cell

In this tutorial, I will show the solving problem when it comes to specific text or a specific word within a text located in a single cell. Our task is to create a formula to display or restore a certain word if it exists in the adjacent cell that contains text.

The situation is shown below.
In the picture below you see the column 'A' in which there is some kind of text. Each cell in column 'A' can contain our criterion within the text. We want the column 'B' to display a certain word or text when it is in the neighboring cell (we want to single out the word).
The picture below is a simple example, you imagine that each cell in column 'A' contains more words of the text which is at first sight difficult to see whether the specific text (our criteria) located within the respective text. Also note that the text of specific character (minus sign) and our criteria are located in different places within the text.

Display a particular text as a criterion if exists within the text in a cell

Find specific text in the cell adjacent for certain criteria

The first way to solve problems is using the auxiliary range of cells containing the criteria/conditions that we are seeking a formula with functions INDEX/MATCH.


In the picture below you see in column 'D' (range 'D2: D3') criteria that we want to find within the text in the cell adjacent. So, we want to check if our particular criterion in the text of each cell.

Return a certain word in the text using wildcard - asterisk
In the picture above you see column 'B' in which the results of the formula for the specific criteria set out in column 'D'.

The formula in cell 'B2' is as follows. (Copy it down. This is ARRAY formula, you should end up with Ctrl+Shift+Enter). This formula uses "Wildcards" (asterisk). You notice that this formula uses a range of 'D2:D3' in which they are located criteria. In the formula is located Wildcards (asterisk).
=IFERROR(INDEX($D$2:$D$3;MATCH(TRUE;ISNUMBER(SEARCH("*"&$D$2:$D$3&"*";"*"&A2&"*"));0));"")

Display a certain text from adjacent cells without auxiliary columns
Another way of solving problem is a formula consisting of SEARCH, IF and IFERROR function.

return a certain word as a condition if exists in the text
The formula in cell 'B2' is as follows (copy down). This formula does not use auxiliary columns because the conditions/criteria implemented in the formula.
=IFERROR(IF(SEARCH("Aero";A2);"Aero");IFERROR(IF(SEARCH("Croatia";A2);"Croatia");""))

The formula below is similar to the formula above and returns the same result
=IF(IFERROR(IF(SEARCH("Aero";A2);"Aero";"");"")="Aero";"Aero";IFERROR(IF(SEARCH("Croatia";A2);"Croatia";"");""))

How to create a formula to display a certain text as a condition

In the picture below you see a situation in which I will show how to create a complex formula of multiple nested functions. Columns 'B', 'C', 'D' are formulas of which we have created a formula in which we are nestled previous formula of the respective columns.

create a formula of multiple functions to display specific words as a condition
The flow of creating complex formulas from more simple formula

The formula in cell 'B2' is next
=IFERROR(IF(SEARCH("*Aero*";A2);"Aero";"");"")

The formula in cell 'C2' is next
=IFERROR(IF(SEARCH("*Croatia*";A2);"Croatia";"");"")

The formula in cell 'D2' is next
=IF(B2="Aero";"Aero";C2)

The formula in cell 'E2' is next
(Final formula returns our objective is as follows. You note that this formula is used "wildcards" - "asterisk" and consists of the previous formulas that are nested within the respective formula).
=IF(IFERROR(IF(SEARCH("*Aero*";A2);"Aero";"");"")="Aero";"Aero";IFERROR(IF(SEARCH("*Croatia*";A2);"Croatia";"");""))

Multiple conditions return specific text if the condition is met

In the picture below you notice the same results as in the previous examples. Created formulas (cells 'G2') is different from the previous ones. Columns 'B', 'C', 'D', 'E' and 'F' are simple formula of which is created the final formula.

Display certain word if met as criteria
Follow the image above and you see all the formulas that were created in certain columns.

To more easily understand how I created a complex formula in the cell 'G2', I'll show you the basic formula that I later nestled in a complex formula. You notice that I used 'Wildcard' in the formula -  "asterisk".

The formula in cell 'B2' is the following. (This formula returns the number 1 if the condition is met ie. If the specific word found within the text in a cell 'A2' and in this case it is the word 'Aero')
=IFERROR(SEARCH("*Aero*";A2);"")

The formula in cell "C2" is the following. (This formula returns the number 1 if the condition is met ie. If the specific word found within the text in a cell 'A2' and in this case it is the word 'Croatia')
=IFERROR(SEARCH("*Croatia*";A2);"")

The formula in cell 'D2' is the following. (This formula is based on the result of cells 'B2' and returns a specific word if the condition is met in this case the word 'Aero')
=IFERROR(IF(B2=1;"Aero";"");"")

The formula in cell 'E2' is the following. (This formula is based on the result of cells 'C2' and returns a specific word if the condition is met in this case the word 'Croatia')
=IFERROR(IF(C2=1;"Croatia";"");"")

The formula in cell 'F2' in the following. (This formula is based on the results from the two cells 'D2' and 'E2' and returns a specific word if the condition is met in this case the word 'Croatia')
=IFERROR(IF(D2="Aero";"Aero";IF(E2="Croatia";"Croatia";""));"")

And finally, to be able to delete all auxiliary columns 'B:F', we will create a complex formula by making a nest all the previous formula.

Thus, the final formula in cell 'G2' is as follows below (copy down)
=IFERROR(IF(IFERROR(IF(SEARCH("*Aero*";A2)=1;"Aero";"");"")="Aero";"Aero";IF(IFERROR(IF(SEARCH("*Croatia*";A2)= 1;"Croatia";"");"")="Croatia";"Croatia";""));"")