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.


[TABLE="width: 655"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Original units[/TD]
[TD]Formula result[/TD]
[TD]Correct numbers (units x pack size)[/TD]
[TD]Multipack Description [/TD]
[TD]Pack size[/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]
[TD]Chocolates 2x250g[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]12000[/TD]
[TD]Chocolates 12x250g[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]



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.