UOM Conversion Formula

[tecwdw]

I have an imported csv file in Excel with a field formatted as "general" that looks like 2 gal 1.47 fl oz
I need a formula that will convert this field to the total number gallons - which in this example would be 2.01148 total gallons.

I'm sort of stumped on how to extract the individual pieces of the field in order to calculate the conversion. I know the alpha characters need removed first probably.

This field may also look like 3.84 gal (with no oz designation)
or 104.96 fl oz (with no gal designation)

Appreciate any suggestions as this is a tedious manual task right now for me.

 

[awoohaw]

Try this:

Mr excel questions 63.xlsm
	A	B	C
1
2	2 gal 1.47 fl oz	2.011484375
3	3.84 gal	3.84
4	104.96 fl oz	0.82
5
tecwdw

Cell Formulas
Range	Formula
B2:B3	B2	=IF(ISNUMBER(FIND("GAL",UPPER(A2))), TEXTBEFORE(A2," gal",1,1,0,0)+IF(ISNUMBER(FIND("FL",UPPER(A2))),TEXTBEFORE(TEXTAFTER(A2,"gal ",1,1,0)," fl",1,1,0,0),0)/128,1*TEXTBEFORE(A2," fl",1,1,1,0))
B4	B4	=IF(ISNUMBER(FIND("GAL",UPPER(A4))), TEXTBEFORE(A4," gal",1,1,0,0)+IF(ISNUMBER(FIND("FL",UPPER(A4))),TEXTBEFORE(TEXTAFTER(A4,"gal ",1,1,0)," fl",1,1,0,0),0)/128,1*TEXTBEFORE(A4," fl",1,1,1,0)/128)

 

[Dave Patton]

You did not specify type of gallons. Edit number for conversion of oz if necessary for your jurisdiction.

another alternative

T202309a.xlsm
	A		C
1
2	2 gal 1.47 fl oz		2.011484
3	3.84 gal		3.84
4	104.96 fl oz		0.82
5
1g

Cell Formulas
Range	Formula
C2:C4	C2	=IFERROR(TEXTBEFORE(A2," g")+IFERROR(TEXTBEFORE(TEXTAFTER(A2,"gal ")," f")/128,0),TEXTBEFORE(A2," f")/128)

 

[tecwdw]

You did not specify type of gallons. Edit number for conversion of oz if necessary for your jurisdiction.

another alternative

T202309a.xlsm
	A		C
1
2	2 gal 1.47 fl oz		2.011484
3	3.84 gal		3.84
4	104.96 fl oz		0.82
5
1g

Cell Formulas
Range	Formula
C2:C4	C2	=IFERROR(TEXTBEFORE(A2," g")+IFERROR(TEXTBEFORE(TEXTAFTER(A2,"gal ")," f")/128,0),TEXTBEFORE(A2," f")/128)

Perfect! Thank you, Dave and awoohaw, for saving me so much time going forward!!!!

 

[Dave Patton]

Thanks for the feedback