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
Improve this question
2
  • Can you edit your source data to include a single quote (') in front of each of those strings?
    – ale
    Commented Jul 29, 2019 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
    Commented Aug 11, 2019 at 19:46

1 Answer 1

Reset to default
2

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.

Improve this answer

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