How to get and fix raw value of a cell that has #ERROR

Question

I have a problem when copy-pasting table data from a web page into Sheets, that the cell values which start with an equals sign ("=") and has wrong decimal delimiter (",") for the locale, for example

=+90,00

causes Sheets to complain about #ERROR! - Formula parse error.

Because I cannot change the source of the data easily and there is a lot of it, I cannot manually remove or change the characters that break the value thinking it is a formula.

Therefore I have been looking for ways to get the cell contents raw to another sheet cell while removing the extra characters.

E.g. on a sheet named RawSource cell A1 I have value (formula)

=+90,00

that causes #ERROR! on that cell. On the destination sheet I tried this formula to fix the error by removing the first character (equals sign):

=RIGHT(CELL("contents", RawSource!A1), LEN(CELL("contents", RawSource!A1))-1)

But no luck so far since I think the ERROR value is propagating even through the CELL function and not the raw contents of the cell.

Is there any way to fix this without manually altering the source data?

Answer 1

once you imported an #ERROR! it can't be corrected by another formula, because that used formula can't see behind that error. workarounds are:

• if you import such data with IMPORTHTML / IMPORTXML / etc. wrap it into REGEXREPLACE or SUBSTITUTE and in some cases even in TO_TEXT with combo of ARRAYFORMULA to get rid off error causing cases
• if it's pasted (as in your case) your only option is to manually add ' upfront of every error cell (eg. '=+90,00)
• also you can always try to paste it in a sheet with a different locale (French, Spain, Czech, etc.) and then copy-paste it to your sheet from there