Search & extract cell data, after & before specific values with a formula

I need a formula to extract data after "<li>Color: " & before"</li>"

	A	B
1	ORIGINAL	RESULTS
2	blah blah blah<li>Color: White</li>some informations	White
3	blah blah blah<li>Color: Red</li>some informations	Red
4	blah blah blah<li>Color: Black</li>some informations	Black
5	blah blah blah<li>Color: Red</li>some informations	Red

I Have already use this formula but I get the "#VALUE!" error even if the cell contains the "<li>Color: </li>" data.

Please Login or Register  to view this content.

Please help me with a working formula.

PS!!!
Sometimes, some cells does not contain the "<li>Color: </li>" data, I don't want the "#VALUE!" error, I just want a blank cell there.

Thanks.

UPDATE!!!
This is an example with the real values I want to extract
Example.xlsx

___________________________UPDATE___________

This is the formula that works (thanks to hemesh):

Please Login or Register  to view this content.

try below in c2
=IFERROR(TRIM(MID(SUBSTITUTE(B2,"<",REPT(" ",100),2),LOOKUP(10^10,SEARCH(":",B2))+2,100)),"")

drag down

Originally Posted by hemesh

try below in c2
=IFERROR(TRIM(MID(SUBSTITUTE(B2,"<",REPT(" ",100),2),LOOKUP(10^10,SEARCH(":",B2))+2,100)),"")

drag down

This formula gives me the entire data of a cell, even if the cell does not contain the "<li>Color: </li>" data

eg:

	A	B
1	ORIGINAL	RESULTS
2	blah blah blah<li>Color: White</li>some informations	blah blah blah<li>Color: White</li>some informations

So this formula does not work at all.


This is what I want:

	A	B
1	ORIGINAL	RESULTS
2	blah blah blah<li>Color: White</li>some informations	White
3	blah blah blah<li>Color: Red</li>some informations	Red
4	blah blah blah<li>Color: Black</li>some informations	Black
5	blah blah blah<li>Color: Red</li>some informations	Red

Please help me with a working formula.

I copied your data without deleting row numbers just change the references
A2 is the cell where your data is
=IFERROR(TRIM(MID(SUBSTITUTE(A2,"<",REPT(" ",100),2),LOOKUP(10^10,SEARCH(":",A2))+2,100)),"")

Originally Posted by hemesh

I copied your data without deleting row numbers just change the references
A2 is the cell where your data is
=IFERROR(TRIM(MID(SUBSTITUTE(A2,"<",REPT(" ",100),2),LOOKUP(10^10,SEARCH(":",A2))+2,100)),"")

Please take a look here:
This is an example with the real values I want to extract
Attachment 319480

try below in B2
=IFERROR(TRIM(MID(A2,FIND(">Color:",A2)+7,FIND("<",A2,FIND(">Color:",A2))-FIND(">Color:",A2)-7)),"")

find always does a case sensitive search if Color and be color than use search instead of find

Originally Posted by hemesh

try below in B2
=IFERROR(TRIM(MID(A2,FIND(">Color:",A2)+7,FIND("<",A2,FIND(">Color:",A2))-FIND(">Color:",A2)-7)),"")

find always does a case sensitive search if Color and be color than use search instead of find

Thank you very much, its working.

Please see attached file.