Search for different strings in a cell to return different results in another cell

[lctradingni]

[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]PRICE PER PACK[/TD]
[TD]NO. PER PACK[/TD]
[TD]PRICE PER UNIT[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]product a 2 x 150ml[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]product b 3 x 200ml[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]product c 6 x 300ml[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]64[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]product d 4 x 500ml[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]product e[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="width: 128"]product f 2 x 150ml[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have a set of data like this. I require a formula that looks for the strings "2 x", "3 x", "4 x" etc in column A and returns the corresponding number in cell c to allow me to quickly work out my cost prices per unit instead of per pack. I've tried loads of formulas but can't get it working. The highest I would really need is up to "6 x" but an explanation of how to add bigger pack quantites e.g. "16 x" etc would be really appreciated. Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about
=IFERROR(TRIM(RIGHT(LEFT(A2,SEARCH("x",A2)-1),3)),"")

 

[Peter_SSs]

.. an explanation of how to add bigger pack quantites e.g. "16 x" etc would be really appreciated. Thanks!

Welcome to the MrExcel board!

Try this copied down. I have not used IFERROR to hide the error value as I thought it might be something you would want to notice if there was no quantity.
I have also included the spaces around the " x " in the SEARCH() so that you don't get a problem if the product name includes that character (eg "Extra big soap")

Excel Workbook
	A	B	C
1	NAME	PRICE PER PACK	NO. PER PACK
2	product a 2 x 150ml	24	2
3	product b 3 x 200ml	13	3
4	product c 6 x 300ml	64	6
5	product d 4500 x 500ml	46	4500
6	product e	12	#VALUE!
7	product f 200 x 150ml	75	200
No per pack

 

[lctradingni]

Hi Peter, Thanks for your prompt reply. This works a treat. Cheers!

 

[Peter_SSs]

Hi Peter, Thanks for your prompt reply. This works a treat. Cheers!

No problem. Glad to help.