Assignmentgeek.com - makes your homework shine. # Extract Specific Text If Match In Adjacent Cell

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

In this Excel tutorial I will show 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 Excel 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. ## Find Specific Text in the Adjacent Cell 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 INDEX / MATCH FormulaFormula.

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

=IFERROR(INDEX(\$D\$2:\$D\$3;MATCH(TRUE;ISNUMBER(SEARCH("*"&\$D\$2:\$D\$3&"*";"*"&A2&"*"));0));"")

## Extract a Certain Text from Adjacent Cells Without Auxiliary Columns

Another way of solving problem is a formula consisting of SEARCH, IF and IFERROR function. 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 with multiple nested functions. In Columns 'B', 'C', 'D' are formulas of which we have created a formula in which we are nestled previous formula of the respective columns. 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. 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";""));"")

Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.