Copying anything with more than one argument out of a blog post or a help page fails immediately, and typing the identical thing character by character works. The file was created by a colleague in another country if that matters. I have checked for missing brackets about nine times and it is not that. What is actually different between the pasted version and the typed one?
I changed a shared file's locale to fix exactly this and broke a column of dates that four people entered over six months. Took me longer to unpick that than it would have taken to retype every formula in the file by hand for a year.
Non breaking spaces are the third one nobody suspects. Documentation sites and PDFs are full of them, they look exactly like spaces, and Sheets rejects them inside a formula. Pasting via a plain text step strips them and about a third of my parse errors turned out to be this.
Before you change the file locale, know what else it moves. Locale drives date parsing, the decimal mark and currency display, so flipping it on a file other people use can reformat dates that were entered under the old setting and quietly change what a number means. Fix your formulas, or make a copy and test the locale change there first.
I would absolutely have changed it on the live file this afternoon. Copying it first.
Practical habit that removed the whole category for me: paste into a notes app with formatting off, then copy from there into the cell. Costs two seconds and kills curly quotes, odd spaces and stray line breaks in one go.
Locale sets the separator. The internet does not know your locale. That is the whole bug.
@xlookup_lena · 3mo ago · 3 replies
Two separate things bite here and you are probably hitting both. The file locale decides the argument separator, and in a number of locales it is a semicolon rather than a comma because the comma is the decimal mark, so every comma you paste is a parse error. On top of that, blog posts often serve curly quotes instead of straight ones, and a curly quote around a string is never valid. Check File then Settings for the locale, and paste into a plain text editor first to see what characters you are actually carrying.
Reply
Report
@csv_apologist · 3mo ago
Worth knowing it works in reverse too. Formulas you write now will paste badly into a colleague's file in the other locale, so if you share these, share them as descriptions rather than as strings.
Reply
Report
@till_and_tally · 3mo ago
It is the semicolon. Colleague set the file up in his own locale and I have been fighting it for a fortnight.
Reply
Report