Find text, then calculate

[Teatro]

I have a spreadsheet that has 3 sizes in a text string in cells. For example,
A2 may have 1 Gal, A3 may have 1 Qt and A4 may have 5 Gal.

I want to create an IF statement that looks for those sizes in the Column A,
and if it finds either 1 Qt or 5 Gal, I want it to take the quantity in
column D and divide by 5 for the 5 gal and multiply by 4 for the 1 Qt. (I'm
trying to convert everything to gallons).

I've tried multiple formulas, but the best I can do is have it report true
or false.

TIA.

 

[Dave]

Hi,
With your data as given, try the following into E2 and copy down.

=IF(ISNUMBER(FIND("Gal",A2)),D2*LEFT(A2, FIND(" ",A2)-1),D2*LEFT(A2, FIND("
",A2)-1)/4)

Regards - Dave.

 

[David Biddulph]

=IF(A2="1 Gal",D2,IF(A2="5 Gal",D2/5,IF(A2="1 Qt",D2*4,"invalid input")))

 

[Teatro]

Still only get either "invalid input" (from your formula) or FALSE (when I
remove the invalid input text). The cell references you have listed are
correct, as is the text, but I still can't get it to work except to return
FALSE (even when it finds the 5 Gal or the 1 Qt in the text).

 

[Teatro]

I originally said that I was trying to convert everything to gallons. This is
incorrect on further review. What I'm trying to do is report on the exact
quantities that were sold. My spreadsheet has already converted everything to
1 gallon quantities. I hope this table below will show what I'm trying to do:

A B C D E
Description Qty Expected Results of IF statement
2 Water 1 Gal 4.00 4 (finds “1 Gal†in A2 and divides D2 by 1)

3 Water 5 Gal 15.00 3 (finds “5 Gal†in A3 and divides D3 by 5)

4 Water 1 Qt .75 3 (finds “1 Qt†in A4 and multiplies D4 by 4)

 

[David Biddulph]

My guess is that the content of your cell isn't what you've said it is.
Perhaps you've got leading or trailing spaces, or some other non-printing
characters?
What does =LEN(A2) return? Should be 5 for the 1 Gal and 5 Gal cases and 4
for the 1Qt.

 

[Teatro]

Sorry, I didn't mean to imply that the only text in A2 was 1 Gal, 5 Gal or 1
Qt.

I hope this table below will show what I'm trying to do:

A B C D E
Description Qty Expected Results of IF statement
2 Water 1 Gal 4.00 4 (finds “1 Gal†in A2 and divides D2 by 1)

3 Water 5 Gal 15.00 3 (finds “5 Gal†in A3 and divides D3 by 5)

4 Water 1 Qt .75 3 (finds “1 Qt†in A4 and multiplies D4 by 4)