Copied pasted number values from WEB are treated like strings

Copper Contributor

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:

return0.PNG

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:returnsome.PNG

 My computer customized format is down below:

 

formatpc.PNG

Excel  options are shown below:options Exce.PNG

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").

 

 

 

4 Replies

Hi,

 

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. 

Just in case, another option could be to keep numbers as it is, but changes formulas. For example, instead of

=SUM(A1:A9)

it could be

=SUMPRODUCT(NUMBERVALUE(A1:A9,"."))

but it very depends on which formulas do you use

 

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...