Extracting words from a cell

[manzier]

Hi all, I'm having trouble with a formula to extract words from a cell. I found out how to do it in cases where the word I want to extract is always in the same place, but have no idea how to do it when the order is variable. See example below



[TABLE="width: 500"]
<tbody>[TR]
[TD]Text[/TD]
[TD]Example 3 Word[/TD]
[/TR]
[TR]
[TD]Example 1: Bacon, Example 2: Sausage, Example 3: Eggs, Example 4: Chips[/TD]
[TD]Eggs[/TD]
[/TR]
[TR]
[TD]Example 3: Tomatoes, Example 5: Bread[/TD]
[TD]Tomatoes[/TD]
[/TR]
[TR]
[TD]Example 2: Tacos, Example 3: Cheese, Example 6: Rice
[/TD]
[TD]Cheese[/TD]
[/TR]
</tbody>[/TABLE]

In the example above I need to extract the word between "Example 3: " and "," but as you can see it can be in any position in the cell.

Any advice? Thanks in advance!

 

[mrshl9898]

How's this?

=MID(A2,FIND(":",A2)+2,FIND(",",A2)-FIND(":",A2)-2)

 

[aaronbens]

If you are always looking for the item after "Example 3" then try:

=TRIM(MID(A2,FIND("Example 3:",A2)+10,FIND(",",A2,FIND("Example 3:",A2))-FIND("Example 3:",A2)-10))