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

[lctradingni]

NAME	PRICE PER PACK	NO. PER PACK	PRICE PER UNIT
product a 2 x 150ml <tbody> </tbody>	24
product b 3 x 200ml <tbody> </tbody>	13
product c 6 x 300ml <tbody> </tbody>	64
product d 4 x 500ml <tbody> </tbody>	46
product e <tbody> </tbody>	12
product f 2 x 150ml <tbody> </tbody>	75

<tbody>
</tbody>

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.