Unit conversions

[newbieexcel24]

Hello all I'm trying to have my spreadsheet automatically convert units:

=IF(ISBLANK(D8),"",IF(ISBLANK(E8),IF(RIGHT(D8,2)="ml",VALUE(LEFT(D8,LEN(D8)-2)),IF(RIGHT(D8,1)="L",VALUE(LEFT(D8,LEN(D8)-1))*1000,IF(RIGHT(D8,2)="kg",VALUE(LEFT(D8,LEN(D8)-2))*1000,IF(RIGHT(D8,1)="g",VALUE(LEFT(D8,LEN(D8)-1)),D8)))),E8*IF(RIGHT(D8,2)="ml",VALUE(LEFT(D8,LEN(D8)-2)),IF(RIGHT(D8,1)="L",VALUE(LEFT(D8,LEN(D8)-1))*1000,IF(RIGHT(D8,2)="kg",VALUE(LEFT(D8,LEN(D8)-2))*1000,IF(RIGHT(D8,1)="g",VALUE(LEFT(D8,LEN(D8)-1)),D8))))))

where E8 is the number of containers.

=IF(ISBLANK(D10),"",IF(ISBLANK(E10),IF(RIGHT(D10,2)="mg",VALUE(LEFT(D10,LEN(D10)-2))/1000),E10*IF(RIGHT(D10,2)="mg",VALUE(LEFT(D10,LEN(D10)-2))/1000)))

However the first formula doesn't work when I try to incorporate the mg conversion, it gives a value error- I think it's to do with the fact I've already have ml conversion as it had worked when I replaced the ml conversion with the mg conversion, they just don't seem to work side by side?

Thanks in advance!

 

[Fluff]

You will need to check for mg before you check for g.

 

[Scott Huish]

You seem to be repeating some checks in that formula.
For example, I see this twice
IF(RIGHT(D8,1)="g",VALUE(LEFT(D8,LEN(D8)-1)),D8)

Can you give some realistic samples of what you are trying to do and expected results. Maybe we can simplify the formula.

 

[newbieexcel24]

Thanks, that solved it!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Peter_SSs]

automatically convert units:

Assuming that you are converting all volumes to "ml" and all masses to "g", what about this shorter option instead?

24 11 16.xlsm
	D	E	F
7	Data	Containers	Result
8	125ml	1	125
9	125L		125000
10	125g	7	875
11	125kg	1	125000
12	1250MG	5	6.25
13	2.45kg		2450
Convert

Cell Formulas
Range	Formula
F8:F13	F8	=LET(u,LOWER(TAKE(TEXTSPLIT(D8,SEQUENCE(10)-1),,-1)),CONVERT(LEFT(D8,SEARCH(u,D8)-1),u,IF(RIGHT(D8,1)="L","ml","g")))*MAX(E8,1)

.. or if there is, or could be, a space between the number and the units, add a TRIM function

24 11 16.xlsm
	D	E	F
7	Data	Containers	Result
8	125 ml	1	125
9	125 L		125000
10	125g	7	875
11	125kg	1	125000
12	1250 MG	5	6.25
13	2.45kg		2450
Convert (2)

Cell Formulas
Range	Formula
F8:F13	F8	=LET(u,TRIM(LOWER(TAKE(TEXTSPLIT(D8,SEQUENCE(10)-1),,-1))),CONVERT(LEFT(D8,SEARCH(u,D8)-1),u,IF(RIGHT(D8,1)="L","ml","g")))*MAX(E8,1)