Errors with thousand separator, decimal separators, and pasting to Excel

New Contributor

I've made a list for documenting purchases which will be debited 5 other business partners. I have a column with a price, formatted as currency. The currency (in Swedish) is displayed as "1 035,50 kr" which is correct. However, when I paste it into Excel, where we will use several formulas to group purchases based on VAT brackets and split the cost up into the 5 business partners, Excel can't sum the values because the thousand separator is not a space which Excel can recognize. If I remove the space between the 1 and the 0 and press the space bar on my keyboard, it can suddenly sum the values.


My solution to this is to remove thousand separators from the column. Looks a bit messy, but it works. However, when I do that, the decimal separator changes from a comma do a period, which Excel also cannot recognize as part of a value, because that's not how decimals are handled in Swedish.


The reason I want to use Lists is because I can use the app and enter costs and edit them when on business trips and whatever. I want to use it as an own reporting tool, and at the end of the month just our economists will copy it over to Excel and send out a couple of invoices. I can't really go over to using Excel for my inputs.


I will probably have to use calculated columns or a bunch of fancy Power Automate flows to make this work, but it shouldn't have to be so complicated.


Update: I used the Unicode-formula in Excel to identify the Unicode characters which are used for the thousand separator. If I press the space bar I get 32, but if I paste the space which is used as a thousand separator I get 160.

0 Replies