How do I paste formulas from a plain text file into a Google Sheet?

Question

When I copy a formula from a text file into a Google Sheet, it shows up as plain text in the sheet rather than being used as a formula.

For example if I copy "=2+3" (not including the quotes) from a text file, and I paste that into a Google Sheet then it pastes as "'=2+3" with a leading apostrophe so that the cell shows the text "=2+3" (not including the quotes).

I have found that "Paste special > Formula only" and "Paste special > Values only" only work when copying from one spreadsheet cell to another. Since I'm copying from a plain text file, those operations do nothing.

I am attempting to paste in thousands of formulas, so typing them in by hand is not feasible. Any help is much appreciated!

Answer 1

I solved my problem. Copy and paste without doing anything special at all works just fine.* The reason that was not working for me initially is that inadvertently the cells I was pasting into had their formatting changed from the default "Number > Automatic" to "Number > Plain text". When I reverted the formatting back to Automatic, the formulas copied in as formulas as desired. I'm glad to know this matters and that I can paste in as Plain text deliberately if I want to in the future.

Answer 2

If you were to paste a single formula, instead of pasting the formula into the cell, paste it into the formula bar, but if you were to paste multiple formulas at once, do the following:

1. Validate that the source file doesn't include extra characters other than those needed to write the formulas and that all formulas are written properly:

• Use English for function names. This will prevent problems. Later you could set the spreadsheet to show the function names in your Google account language.
• If your source file has values, ensure that values of type date, time, duration, etc., are written in a non-ambiguous format. If that is impossible, double-check that all of them are appropriately written; if you don't have experience doing this and don't want to do it back and forth multiple times, the best to use a simple text editor as apps having rich text might insert hidden and replace characters depending on multiple factors.

2. Validate that the target spreadsheet settings match the locale used in formulas and values
3. Set your spreadsheet to show formulas using English.
4. Paste the formulas / values
5. Use find and replace to remove the leading apostrophes. Set the find and replace option to search in formulas and use regular expressions. Use the regular expression ^':
   • The caret symbol ^ means to look at the beginning of the cell formula / value. This will prevent eliminating apostrophes that might be used on text values (character sequences between double quote straight character) used in fórmulas and values.
6. Validate that everything is working OK.
7. Optional : Change the spreadsheet settings to show the function names using your Google account language instead of English.