Find nxn in a text and run the calculation

[mils]

Hi,

I have the a column (A1:A5) with the following text:
Can Al NNN 330 1x6 LowLands NL 1503
Can Al AAA 330ml 4x6 NL 1403
Can Al BBB 330ml 2x12 NL 1403
Can Al CCC 500ml 4x NL 1403
Can Al BBB 330ml x12 NL 1403


I would like to create an extra column with the different opperation (nxn), So the next column (B1:B5)
6
24
24
4
12

there are some cases where I'm missing either (xn= or (nx) in these cases I just need to show n.

If the function can't find nxn the value should be 1.

I've attached an example.

Thanks for your help.

Book2.xlsx

[AlKey]

Hi mils,

Try this array formula in B1 and copy down

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Data Range

	A	B
1	Can Al NNN 330 1x6 LowLands NL 1503	6
2	Can Al AAA 330ml 4x6 NL 1403	24
3	Can Al BBB 330ml 2x12 NL 1403	24
4	Can Al CCC 500ml 4x NL 1403	4
5	Can Al BBB 330ml x12 NL 1403	12

[samba_ravi]

or can try
=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("x",A1)-1)," ",REPT(" ",LEN(A1))),LEN(A1)))+0,1)*IFERROR(TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("x",A1),)," ",REPT(" ",LEN(A1))),LEN(A1)))+0,1)

Please Login or Register  to view this content.

[mils]

Hi Alkey,

thanks for your answers. The formula worked well for this 4 examples but I forgot to mention that I have also this kind of text
Can Al dddd Vintage 330ml Mix 2015
Can Al wwww Max 330ml 2014
Can Al xxx Max 500ml BG

which contains x.

I've attached an example...[

Book2.xlsx

[AlKey]

Here is a modified formula

Please Login or Register  to view this content.