Best way to convert data from an unknown unit (cm OR inches) ?

Here is another option:

Enter your length in column B. Columns C and D convert it to inches and centimeters.

Formula in C2 =IFERROR(CONVERT(REGEX.EXTRACT($B2,"[\d.,]+"),REGEX.EXTRACT($B2,"[a-zA-Z]+"),C$1),"")

Fill to D2 then fill down to complete the columns

Format column B as text if you ever want to use "m" for meters, otherwise Numbers will think it is minutes.

(I edited the formula after I posted it originally so that it now uses the units in C1 and D1 to determine what the columns should be)

You would need a third column, call it whatever you like. Anything else would be self-referential.

The other option is to create the conversion formula in each column, so that every cell in "inches" has the formula to convert the adjacent cell. And vice versa. But, then you're overwriting the formula when you add the actual number. Eg. you would type 5 in inches which would replace the formula with 5. That might be ok in your case, but the third column is non-destructive.

create a third column. Probably use an if statement, akin to:

(if IN > 0 then convert, if cm > then 0 then convert, 0)

Not the actual formula

Another option:

This one uses two input columns, one for values in Inches, the second for values in cm. In each row, the cell not matching the unit for the entered value is left empty. The numerical value only is entered in the column matching its unit.

Columns C and D similar formulas that sum the entered value in the entry column matching the unit of 'this column' and the converted value of 'the other column'. Formulas for these two columns are shown below the table as entered in C2 and D2, and filled down from there.

Regards,

Barry