Log In
Register
Home
Active Member
September 13, 2020
Offline
Hi all
I'm using Excel for Mac (MSO365), and downloading source data which separates decimals with full stops, common practice. My Mac's functions don't recognize the cell references as numbers unless I find and replace all with commas. The data sets are large, and so find and replace takes >30min to complete! I've read that one can change your global settings to read full stops instead of commas, in system preferences>language and region>advanced>number separators. For some reason this isn't working (ensuring Excel is closed while affecting, and re-starting Mac).
Any Mac users, or anyone for that matter with a hack?
See Answer
July 16, 2010
Offline
Hi Warrick,
I suspect you also need to change the locale in Excel (File > Options > Language) to match the language settings in your operating system. That said, this will only fix the issue temporarily unless you're willing to keep your Mac set to always interpret commas as decimals.
Mynda
Active Member
September 13, 2020
Offline
Thanks Mynda, I tried this (what I meant by "global settings") but a simple test of the number values thereafter revealed it didn't work. Seems I'll have to simply use "find and replace" as part of cleaning data prior to appending onto main table.
Trusted Members
December 20, 2019
Offline
if the data is exported from a system with commas and imported as a text file with the comma, it is likley that excel is just treating it as text and cant convert it.
can you upload a sample of the raw data?
Trusted Members
Moderators
November 1, 2018
Offline
How exactly do you download the data set?
Active Member
September 13, 2020
Offline
Hi both
Apologies I'm not familiar with this forum's format and replying to you individually.
@Purfleet, sure I've attached a sample of the data.
@Velouria, downloaded directly from our ERP. I save the .txt file to my desktop, then right-click and "open with>Excel." I then "save as" XLSX file.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Notice when opening the .txt file following your steps that number values containing period (.) is regarded as text while those only containing numbers are treated as numbers. But when doing a data import I was able to state that the period is the decimal divider and the comma the thousand divider and after that the numbers in the import shows correct, see attached pictures.
Screenshot 1.jpeg shows the result after the data import.
Screenshot 2.jpeg shows the result after following your steps.
Br,
Anders
Answers Post
The following users say thank you to Anders Sehlstedt for this useful post:
PurfleetActive Member
September 13, 2020
Offline
Hi Anders
Thank you, this sounds like a real solution! Could I please ask you show me how to do this step, never done it:
"But when doing a data import I was able to state that the period is the decimal divider and the comma the thousand divider..."
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Go to Data menu, on the left hand side you find From text, click on that and the Data import guide starts.
Navigate to your .txt file and open it. Just click next on the first step, in the second step click next again, in the third step you need click on Advanced button and there change to desired decimal and thousand delimiters. See attached pictures for each step.
Br,
Anders
1 Guest(s)
