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").
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?
=SUMPRODUCT(NUMBERVALUE(A1:A9;".")) works, but it doesn't change dots and commas, while formulas work, they it is need to be comma as separator on the final paper. I have a feeling i'll have to school them...