Invalid value (Problem with RIGHT, SUBSTITUTE and VALUE Formula



As the picture below shows, I am trying to extract 2 numbers from a cell that displays different formats (paper sizes). I need to set up a calculation with the 2 numbers. That is why I use the LEFT and RIGHT Formula to extract the text and the VALUE formula to change it to number format.

I also use the SUBSTITUTE formula because I need the decimal comma (",") instead of decimal point ("."). As you can see, the LEFT formula works perfectly, but the RIGHT formula hits an error (INVALID VALUE), when I add the VALUE formula.

LEFT formula:

=VALUE(SUBSTITUTE(LEFT(FORMAT@row; (FIND(" "; FORMAT@row; 1) - 1)); ","; "."))

RIGHT formula (with error):

=VALUE(SUBSTITUTE(RIGHT(FORMAT@row; LEN(FORMAT@row) - FIND("x "; FORMAT@row; 1)); ","; "."))

Has anyone an idea, what I'm doing wrong?

Thanks in advance, Kilian

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!