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?
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
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.
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?
How exactly do you download the data set?
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.
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
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..."
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
Hi Anders
A big thank you, this works perfectly! Appreciate.
