Substitute+concantenate formula adding character when saving

[Seth_Berman]

have gone back to work on an app i havent touched since early 2020 and noticed that this formula:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(RIGHT([client],4),NOW()),"/",""),":","")," “,”") is doing something odd when saving to the google sheet. it is producing an extra character, an apostrophe at the beginning of the string. It did not do this before and i was wondering if something had changed.

when I look at the google sheet i don’t see the apostrophe until I click in the cell and look at the formula bar. '639602242021161947.

[Markus_Malessa]

Most commonly I have seen the apostrophe in Excel, designating a number formatted as text. I wonder if that is your case and the same is true in Google Sheets.

[Seth_Berman]

Good thought. I checked and the entire column is formatted as plain text in the sheet, however, the entries you see above do not have the apostrophe and they were all generated using the same expression in AppSheet.

[MultiTech]

Markus_Malessa:

Most commonly I have seen the apostrophe in Excel, designating a number formatted as text. I wonder if that is your case and the same is true in Google Sheets.

This is the same case yes; change the column type in your sheet to match the column type in your app.

Seth_Berman:

the entries you see above do not have the apostrophe and they were all generated using the same expression in AppSheet.

Happens, oddly enough. If I remember right: my solution to this was to copy the whole column into another column… or maybe us a formula like =A3 or whatever - to pull the value into the new column. Then copy THOSE values back into the original and set the column type to number.

[Steve]

If this is a change in behavior, please contact support@appsheet.com for help with this.

[Seth_Berman · Accepted solution]

the issue ended up being in the spreadsheet. thank you everyone for your suggestions, it pointed me in the right direction.