Distinguishing between 2x and 12x with IF(ISNUMBER(SEARCH

[Mr2017]

Hi

I have a sheet where I'm extracting the pack size of a product using IF(ISNUMBER(SEARCH then multplying that pack size by the units of the product.

However, there are some products where the pack size is 2 and others where it is 12 eg

Chocolates 2x250g
Chocolates 12x250g

Excel can't seem to distinguish the difference between the 2x and 12x?

I used the data below - if you copy and paste it in cell A1, it should go up to column E.

Original units	Formula result	Correct numbers (units x pack size)	Multipack Description	Pack size
1000	2000	2000	Chocolates 2x250g	2
1000	2000	12000	Chocolates 12x250g	12

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The formula I used is in cell B2. However, when you drag it down to B3, it doesn't work? Does anyone know how to fix this? Thanks in advance.

=IF(A2="","",IF(ISNUMBER(SEARCH("2x",D2)),A2*2,IF(ISNUMBER(SEARCH("12X",D2)),A2*12,"")))

 

[MARK858]

What about...

=IF(A2="","",IF(ISNUMBER(SEARCH(" 2x",D2)),A2*2,IF(ISNUMBER(SEARCH("12x",D2)),A2*12,"")))

 

[Scott T]

Because your are searching for 2X in a string that contains 2X (12X) your first isnumber will be true for 2X and 12X
Try testing for 12X first so that if it is 2 you get false with the first isnumber.

=IF(A2="","",IF(ISNUMBER(SEARCH("12x",D2)),A2*12,IF(ISNUMBER(SEARCH("2X",D2)),A2*2,"")))

 

[Mr2017]

Thank you both!

I tried what Mark 885 suggested

i.e putting a space before the 2 in the 2x case.

and it worked!

I will also consider using the method you suggested, Scott T, in future.

Thanks again for both responding.