Converting weights/volume to numerical value

[newbieexcel24]

Hi,

I'm trying to convert cells of weights/volumes to a numerical value ie 7kg becomes 7000. I don't understand why the formula below doesn't work for 'ml'. The formula i'm using is:

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

I have attached a screenshot.

 

[Joe4]

You need to re-order your formula and checking for various values.
You need to check for "ml" BEFORE you check for "L". Otherwise, it will stop at the "L" check and never get to the "ml" check.

 

[Fluff]

You need to move the ml & g section of the formula before the L & Kg parts

 

[Joe4]

You need to move the ml & g section of the formula before the L & Kg parts

Good catch on the "g" part too!

 

[Fluff]

Actually I was wrong, the g & Kg need to stay as they are, just the ml needs to be moved before the L

 

[Joe4]

Actually I was wrong, the g & Kg need to stay as they are, just the ml needs to be moved before the L

Yes, you are correct. Looks like they already have that set in proper order - just the "L" and "ml" need to be changed.

 

[newbieexcel24]

Well that's a bit embarrassing thank you

 

[Joe4]

You are welcome.
Glad we were able to help!