LinkedIn
Twitter
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.
Solved
1 ACCEPTED SOLUTION
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.
