I have several Excel workbooks that scrape text and use VALUE(string) to convert the text to numbers. I want to convert them to Google sheets. Unfortunately, many of the numerical fields have leading +/-'s, and Google Sheets chokes on a leading +.
An ugly workaround is
VALUE(SUBSTITUTE(string),"+","")
but does anyone have something more concise and/or robust?
Use the double unary operator, like this:
=--"+5"
To coerce a value in a cell to a number, add a reference to the cell, like this:
=--A1
This has the benefit that it will retain number formatting, unlike value().
The reason why the leading plus sign is treated in a special way is probably that the plus sign is interpreted to start a formula when it is the very first character that appears in a cell. For example, this is a valid Google Sheets formula:
+2 -1 +4 -3
The first plus sign will be converted to = and the expression yields 2, as expected.
To place a plus sign as the first character without the expression getting interpreted as a formula, add a leading single quote '. The quote will not show in the spreadsheet and it is ignored when the cell is referenced. For example, consider this expression that yields an international telephone number as a text string:
'+1 555 123 4567
The value will show as +1 555 123 4567 in the spreadsheet, and the len() function will tell it is 15 rather than 16 characters long.
If you try to enter the same without the leading ', you will get a formula parse error.