What am I missing?

[nilaco]

I created this formula to convert all the values to mm whether they're listed as "imperial", "metric", "cm" or "inch" in column L. It works pretty well, but if the value is already listed as "mm", the formula adds an extra "0" to the end of the number (see row 9).

The formula I've used in columns P,Q & R is:

=IFERROR((IF(OR(L2="imperial",L2="inch"),(ROUNDUP(CONVERT(I2,"in","mm"),0)),(CONVERT(I2,"cm","mm")))),"")

 

[jasonb75]

The formula I've used in columns P,Q & R is:

=IFERROR((IF(OR(L2="imperial",L2="inch"),(ROUNDUP(CONVERT(I2,"in","mm"),0)),(CONVERT(I2,"cm","mm")))),"")

What are you expecting CONVERT(I2,"cm","mm") to do?
Converting 760cm to mm will multiply by 10.

 

[nilaco]

I see what you mean. Essentially what I need the formula to do is convert all the values to "mm" whether they're listed as "inch", "imperial", "cm", "metric" or "mm". I guess I need to insert an extra bit saying that if I2 is listed as "mm" then it should remain the same, but i'm not sure how I can work that into the formula.

 

[nilaco]

Where i'm stuck is that IF statements are based on whether whats written is true or false, but what I need I guess what I want to write is:

IF L2 = "imperial" or "inch", then convert inch to mm (and round up to the nearest whole mm)
IF L2 = "metric" or "cm", then convert cm to mm (and round up to the nearest whole mm)
IF L2 = "mm", then don't change a thing
then if the cell is blank or contains "-", then the cell should remain blank

 

[nilaco]

Sorry ignore that last post, i deleted part of a sentence by accident!!

Where i'm stuck is that IF statements are based on whether whats written is true or false, but I need it to be based on three possible outcomes. I guess what I want to write is:

IF L2 = "imperial" or "inch", then convert inch to mm (and round up to the nearest whole mm)
IF L2 = "metric" or "cm", then convert cm to mm (and round up to the nearest whole mm)
IF L2 = "mm", then don't change a thing
then if the cell is blank or contains "-", then the cell should remain blank

 

[jasonb75]

I think that this should do it, I've tried it with a couple of values for each of the different descriptions in your list.

=IFERROR(ROUNDUP(CONVERT(I2,SUBSTITUTE(SUBSTITUTE(LEFT(L2,2),"Im","In"),"Me","cm"),"mm"),0),"")

You could do it with more IF's and OR's, but as you've found, that tends to start getting a bit messy.

 

[nilaco]

Thank you! Would you mind explaining how this formula works if you have the time?

 

[nilaco]

Hmm I've just applied it to the spreadsheet and only works for "cm" "inch" and "mm", but changes it to a blank cell for "imperial" and "metric"...

 

[jasonb75]

I had never paid attention to the fact that SUBSTITUTE is case sensitive (until now).

A slight change and all appears to work well.

Book1
	I	J	K	L				P	Q	R
2	29.2	44.5	19.1	cm				292	445	191
3	58.4	71.6		metric				584	716
4	18	23.5	32.5	imperial				458	597	826
5	9	13.5	14.3	inch				229	343	364
6	760	925	645	mm				760	925	645
Sheet2

Cell Formulas
Range	Formula
P2:R6	P2	=IF(ISNUMBER(I2),ROUNDUP(CONVERT(I2,SUBSTITUTE(SUBSTITUTE(LEFT(LOWER($L2),2),"im","in"),"me","cm"),"mm"),0),"")

The formula works by taking the first 2 (LEFT,$L2,2) characters of the unit of measurement, then SUBSTITUTING them with a valid unit in the case of (me)tric or (im)perial which are change to (cm) and (in) respectively.
This then gives a valid measurement unit to be converted, either 'in' for inch or imperial, 'cm' for cm or metric and mm remains unchanged.
I also noticed that you mentioned empty cells earlier, so I've changed the formula slightly to allow for that. An empty cell wouldn't cause an error so the original method would have given a 0 result in such cases.

 

[nilaco]

Ah yes this is perfect!! Thank you @jasonb75 !!