Formula Parse Error in Plain Text Cells in Google Sheets

[Anton_Dickens]

Hi everyone,

I’m having a little bother with the Google Sheet behind my app.

I have a Notes column which is a long text column. The data goes to a column in my Google Sheet which is set as plain text. The problem is that if any of our users enter “+” as part of what they write (and I’m sure other characters Sheets identifies as parts of formulas), it returns a formula parse error. So in the notes they might write “+ $500 for traffic control”. Is there any further way to tell Sheets to ignore this and treat all characters as plain text? Or do I have to tell the users to avoid these characters?

Many thanks
Anton

[Marc_Dillon]

I believe as long as the “+” or “=” is not the FIRST character in the field, then all will be fine. I’d suggest writing a valid_if expression for that column to prevent this:

NOT( IN( LEFT( [_THIS] , 1 ) , LIST( “=” , “+” ) ) )

[hodgson_doug]

Might try a condition that tests for the special characters, and instead add an apostraphe (’) to the front, explicitly telling Google Sheets to treat it as text, regardless of how mathematical it looks.

[Marc_Dillon]

My first thought was also to use SUBSTITUTE() to get rid of the offending character. Then I realized that would only be possible in a second column, and the originally entered data would still have to exist in a column in the spreadsheet, which would bring about the same problems probably.

…unless maybe if you ran a data change action upon form save, hmmmm.