2

I'm trying to copy a table of data from a website into Google Sheets. The table includes a column whose numbers are preceded by +/-, e.g., +/- 33. Excel works just fine, allowing me to paste in the table, and it correctly interprets the column as text.

However when I paste into Google Sheets, it interprets this as a broken formula, showing #ERROR for each column, and issuing a Formula parse error. Is there any way I can paste a table and prevent sheets from converting the text to formulas? Example

Sample source data to copy and paste to duplicate the problem:

+/- 1
+/- 456
  • Can you edit your source data to include a single quote (') in front of each of those strings? – ale Jul 29 at 23:00
  • @ale I had actually mentioned that I tried that unsuccessfully in my original edit of the message but took it out because it seemed irrelevant. So no, that won't work thanks :) – glenviewjeff Aug 11 at 19:46
1

You can copy paste the data in a tab free of data in column A.
Then in column B1 you can use this :

=REGEXREPLACE(formulatext(A1),"=",)

enter image description here

Copy the cell for every data you have in column A
Copy Column B then paste with the option values only

Explanation :
FORMULATEXT() is self-explanatory : it returns a formula based on a cell I.E. the string =+/- x here.

To get rid of the "=" sign, REGEXPLACE is a formula who can search for a string or a regex then replace it with whatever suits your need.
In our case we look for "=" then replace with nothing.


Why I didn't used ARRAYFORMULA to do this :

ARRAYFORMULA() works for any function that takes a range (e.g. A1:B6) as an input parameter. Then it will also accept an array in its place.
Infortunately FORMULATEXT() requires a cell as a parameter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.