Excel formats data post-sync

I have an issue with Excel as a spreadsheet for my Appsheet app. It seems like Excel really wants to guess what kind of data I have and format it as such. Unfortunately it happens to guess wrong sometimes.

I have a “location” column in my app which is formatted as:
[Row] - [Number] - [Floor]

For instance a location could be “1-1-1”.

Unfortunately when this syncs to Excel, it thinks it’s a date so instead of keeping it as “1-1-1” it changes it to “01-01-2001 00:00:00” which obviously makes no sense for a location.
Is there any way to prevent this? I know Excel has formatting options, but these always seem to revert back to their default setting so I’m out of luck there. In Appsheet the column is selected as “Text” and I could also select that in Excel, but as I said, after a while it changes it back to “General” which then turns it into a date, so is there any other way to prevent this formatting?

Thanks in advance

Here are some pictures that shows the issue:
Appsheet column:

Excel sheet: The last column (5) is a concatenation of column 2, 3 and 4. Column 1 is just an ID.
Excel

Not tested through “in app” updated with the backend being Excel. But tested the below on Excel in separation with manual data entry. So it may not be a confirmed solution.

Is it possible to have the location expression as

CONCATENATE([Reo], " - ", [Plads], " - ", [Etage] ) and then having the Excel column formatted as Text.

Basically the suggested expression has extra spaces on both sides of the hyphen so that Excel does not think it as a date.

As you can see in the screenshot below, the cell highlighted in yellow, Excel thinks it as a date entry while the cell above that does not show any error. There are extra spaces in the cell without error.

Excel Formatting

2 Likes

or

CONCATENATE (" ’ ",[Reo], “-”, [Plads], “-”, [Etage])

It is necessary to use quotation marks to show an expression as TEXT in the Excel cell. Example: 150 is a number. But '150 is the text for Excel. And excel doesn’t show the leading quotation mark.

3 Likes

@HBT @Suvrutt_Gurjar

Thank you both for your suggestions. I’ll try out the solution suggested by @HBT

3 Likes