IF, LEFT, and SEARCH combination formula.

[GoodWood]

Good day folks,

Please help in my query if you can. Thanks!
Essentially, I am transcribing historic trade merchandise and goods from ships inventories. The commodity I am researching is the button. The inventories mainly list button quantities in pairs (N=2), dozen (N=12), gross (N=144), great gross (N=1728), or double gross (N=1728).

I key in select terms from the inventories into a cell (P162), beginning with the quantity (#) and type of quantity ("Gro"). These two data entries are then followed by numerous other associated button data.

I am using a combination of formulas to to extract the quantity and type of quantity. The current formula in R162 is:
=IF(ISBLANK(P162),"",LEFT(P162,SEARCH("Gro",P162,1)-1))
This searches P162, which says "1 Gro Coat Buttons". The formula then returns "1" in R162.

This works fine for the quantity type Gross (abbreviated "Gro"), yet it requires me to change the formula in the R column for an other quantity type. Ex. =IF(ISBLANK(P986),"",LEFT(P986,SEARCH("Doz",P986,1)-1)).
So, is there a formula that is able to search multiple SEARCH criteria using a LEFT formula? i.e. "Pr", "Doz", "Gro", "Gt Gs", "Dbl G". Please let me know if you require any clarification. Thank you for your time.

GoodWood

 

[FDibbins]

See if this will work for you. In involves making a small table with Type and Qty, and only uses up to a 3-letter code. This worked in testing on some sample data
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Item[/td][td]Qty[/td][td]Type[/td][td]Vol[/td][td][/td][td]Pr[/td][td]

2​

[/td][/tr]

[tr][td]

2​

[/td][td]1 Gro Coat Buttons[/td][td]

1​

[/td][td]Gro[/td][td]

144​

[/td][td][/td][td]Doz[/td][td]

12​

[/td][/tr]

[tr][td]

3​

[/td][td]12 Pr Coat Buttons[/td][td]

12​

[/td][td]Pr[/td][td]

24​

[/td][td][/td][td]Gro[/td][td]

144​

[/td][/tr]

[tr][td]

4​

[/td][td]123 Doz Coat Buttons[/td][td]

123​

[/td][td]Doz[/td][td]

1476​

[/td][td][/td][td]Gt[/td][td]

1728​

[/td][/tr]

[tr][td]

5​

[/td][td]1234 Gro Coat Buttons[/td][td]

1234​

[/td][td]Gro[/td][td]

177696​

[/td][td][/td][td]Dbl[/td][td]

1728​

[/td][/tr]

[tr][td]

6​

[/td][td]1 Gt Gs Coat Buttons[/td][td]

1​

[/td][td]Gt[/td][td]

1728​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]1 Dbl G Coat Buttons[/td][td]

1​

[/td][td]Dbl[/td][td]

1728​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
F:G is the table
B2=--TRIM(LEFT(A2,FIND(" ",A2)))
pulls the Qty
C2=TRIM(MID(A2,FIND(" ",A2)+1,3))
pulls the type
D2=IF(A2="","",B2*VLOOKUP(C2,$F$1:$G$5,2,0))
calcs the Volume

You could, of course, do all 2 formulas in 1...
=IF(A2="","",--TRIM(LEFT(A2,FIND(" ",A2)))*VLOOKUP(TRIM(MID(A2,FIND(" ",A2)+1,3)),$F$1:$G$5,2,0))

And could probably also do away with the lookup table using CHOOSE, but then it starts getting more involved (KISS)

 

[Aladin Akyurek]

Looks like you are after something like this...

=IF(P162="",LOOKUP(9.99999999999999E+307,SEARCH({"Pr", "Doz", "Gro", "Gt Gs", "Dbl G"},P162),{2,12,144,1278,1278}))

Right guess?

 

[GoodWood]

Thanks guys!

These are both very ingenious ways to do what was asked. Neither one does precisely what I would like, since I was not clear in my request. I will think on this for a short while and then see how to better write what I am looking for. Thanks again.

GoodWood