03-02-2018 02:18 AM
03-02-2018 02:18 AM
Hello, I would like to ask about an issue I have while copying and pasting information from the WEB page to excel spreadsheet.
The information I am copying is table, with some strings and some floats. In the end what I need to do is write down =sum() and it should add everything in the column. However i noticed that in the WEB page floats decimal symbol is dot (".") and in the excel I need it to be comma (","). That causes me an issue with formulas, because formulas think that i try to sum up a strings not floats. So return value always end up 0. The image below is an example:
If i manually change dot to comma excel recognizes these as floats and function works. However my work sheet looks more complicated with a lot of values, and changing each one of the by hand is no go solution. The example of changing values by hand below:
My computer customized format is down below:
Excel options are shown below:
If I try to deselect "Use system separators" and use written custom separators it only effects those which are changed by hand (From dot to comma). If I try to change text format from "General" to "Numbers" it does not effect the formula and number. If i try paste special and use text, it will become a mess, on one column all the info, you don't want to see that believe me. And if I try paste special Unicode it has zero effect as well.
So is there a fix for my problem? I found a lot of work around, but my clients are not that bright to use them, (I am talking mostly about elder people with a attitude of "You don't teach me how to do my job, boy").
03-02-2018 04:25 AM - edited 03-02-2018 04:28 AM
The numbers you import from the web are NOT numeric numbers in Excel, Excel treats them as texts.
You see them aligned to the left, and this is the default alignment of any text in Excel.
When trying to sum them, SUM function returns 0 since there are no numbers to add!
The reason that makes Excel treats them as texts is the difference in the default decimal separator on your computer, and the decimal separator used in the imported numbers.
Anyway, leave the default decimal separator as it is "," .
The solution is so simple:
Select all the numbers, and press Ctrl+H to open the Find and Replace tool.
Type "." in Find what box, and type "," in the Replace with box, and then hit Replace All.
03-02-2018 05:30 AM
Just in case, another option could be to keep numbers as it is, but changes formulas. For example, instead of
it could be
but it very depends on which formulas do you use
03-05-2018 11:44 PM
This is a good answer, of course I can find and replace separator. Isn't there any auto change if you have 'a' change to 'b'? Because my client has a co-worker that just copy paste the same data ant it's comma not a dot as separator. I wonder why?
03-05-2018 11:46 PM