Any suggestions as to how to copy-paste tables from websites and have the comma removed (or changed to into a dot) ?
Copy 1,000,000 from a website results in 1 in Excel, i.e. 1 (one) instead of 1 million.
Note: Windows > Control Panel > Region > Advanced format > Customise format: digit grouping symbol reads dot.
Regretfully the comma's are still copied over.
Hi there
check advanced editing settings IN EXCEL (Office --->options etc not Windows settings) -- decimal point / comma uses are different depending on region -- e.g Europe, S.America etc 100,00. UK/USA 100.00
You should be able to set decimal and Thousand's separator in EXCEL (not sure what Office/365 does but surely it has to be similar to Office 2016/2019).
Cheers
jimbo
Last edited by jimbo45; 04 Apr 2020 at 03:38.
Am I misunderstanding something here? I have no issues in keeping the original number formatting.
I copy the numbers, then paste in Excel keeping the original formatting:
(Click to enlarge.)
Kari
Hi there
I think the OP wanted not 1,000,000 to be copied as 1,000,000 but 1.000.000 --- you have to muck about with some regional / language/editing settings in EXCEL -- UK English for example will treat 1,000 etc as 1 (USA too I'd imagine and most other variants of English) since it will assume the comma is the decimal separator rather than '.' i.e 1,07.00 for example rather than 1.07,00
Cheers
jimbo
For good order's sake, it is about copy pasting from tables from websites.
I'll try to explain with an example.
Let's take the data from Coronavirus Update (Live): 1,133,801 Cases and 60,398 Deaths from COVID-19 Virus Outbreak - Worldometer
For Europe the thousands separator normally is a dot.
Changing the language of Office 365 into English makes no difference.
Screenshot 1:
- you will notice that the '0' (3rd digit after the comma) is stripped
- also when adding a number to the data it will get wrong results, either an error (Value#) or when adding just 1, Excel expects this to be 1000
Pasting as text leaves the comma in place.
My 'workaround' is to go into the clipboard manager, do a find and replace (comma with nothing) and then copy paste from the clipboard manager. I was hoping there would be kind of 'settings solution' though.
Hi thereFor good order's sake, it is about copy pasting from tables from websites.
I'll try to explain with an example.
Let's take the data from Coronavirus Update (Live): 1,133,801 Cases and 60,398 Deaths from COVID-19 Virus Outbreak - Worldometer
For Europe the thousands separator normally is a dot.
Changing the language of Office 365 into English makes no difference.
Screenshot 1:
- you will notice that the '0' (3rd digit after the comma) is stripped
- also when adding a number to the data it will get wrong results, either an error (Value#) or when adding just 1, Excel expects this to be 1000
Pasting as text leaves the comma in place.
My 'workaround' is to go into the clipboard manager, do a find and replace (comma with nothing) and then copy paste from the clipboard manager. I was hoping there would be kind of 'settings solution' though.
did you UNCHECK "Use system separators" then change Decimal and thousands separator to ',' and '.' respectively
I was also going to suggest the long winded way -- copy as text and then use EXCEL to save as CSV file - edit replace ',' '.'
This might work for a "Static" web file e.g your examples shown in your post but if you want a dynamically changing sheet --e,g Stock exchange values, Currency (forex) values etc you'll need to ensure the excel file treats ',' as '.' and the other way around.
Cheers
jimbo
Thanks again. Probably there is no solution. Again one of those 'comma' issues. Office is a nice product, but it being an American company, solving such issues is at the bottom of their priority list (or maybe it isn't at all at the priority list).
I have unchecked that settings, in both English and Dutch 'version', I have tossed the separators, checked and unchecked.
Problem remains.
Another example: Exporting Outlook contacts data to .csv - edit data in Excel, export to .csv again and then import into Outlook will result in issues.
csv does not container comma's but semi colons...
if you do a find and replace semicolons with comma, this will also go wrong because the Dutch Outlook version has a comma in the address fields.
Where the English version reads: Home Address Street, the Dutch version reads: Huisadres, straat
(Home Address=Huisadres, Street=Straat)
Because of the comma in 'Huisadres, straat' data ends up in the wrong columns.
This counts for Home Address City, Home Address Postal Code, etc.
In that sense one Office product isn't compatible with the other.
Anyway, that aside, I'll continue to use the workaround.
Thanks again!
You are soo right!
and I was soo wrong.
I have to confess...
For, I-don't-know-how-long, I wrongly assumed that the decimal and thousands separators were 'part' of the system separators. Hence, by unchecking system separators, well, eh, I thought the two other components (decimal/thousands) were not taken into consideration.
Yes, I admit, it is unbelievable.
I did change them, but as the copy paste still looked the same I did not check any further, so I didn't notice that the numbers were correct after all (though with comma as thousand separators). Simply change cell properties to 'Default' - problem solved.
MANY thanks indeed!
Quote
