Wanting to find 2 different forms of text that are similar and make them appear in another cell

[Jaffabfc]

Hi,

I currently have several thousand lines of data in column B and they have descriptions of paper/exercise books etc.

I am wanting in column C it to say if it can find in column B if it has A4 or A4+ or A5 (etc) in the text and pull it back to the cell.

So B2 says "Red book a4 200 pages" in C2 i want a formula to say its found a4, so the formula would say A4
But in B3 i could have "red books a4+ 100 pages" in C3 i would like the fomrula to pull back the answer of A4+.

Ive done it loads before just minds gone blank.

thanks

 

[Jaffabfc]

also some cells in column B have a4 then a space and a plus, without changing the data i am wanting that picking up as a4+ too

 

[shift-del]

Try this.

Mappe21
	B	C	D	E	F
2	Red book a4 200 pages	A4			A4
3	red books a4+ 100 pages	A4+			A4+
4	calendar A5	A5			A4 +
5	calendar A5 +	A5+			A5
6					A5+
7					A5 +
Tabelle1

Cell Formulas
Range	Formula
C2:C5	C2	=SUBSTITUTE(LOOKUP(2,1/SEARCH($F$2:$F$7,B2),$F$2:$F$7)," ","")