When Copy/Paste Web content (containing numbers) in to excel, can't use copied numbers for calculation!

[VraGolan]

When I copy and paste text from a web page in to excel, the numbers in the cells are not recognized as numbers and therefore no calculation is possible (#VALUE error) by using this numbers.
I have tried solutions like changing cell format, Paste Special multiply or add 0, Value() Function, Text to Columns etc. but non of them is working.
The only formula that could transform this text in to numbers was =0+SUBSTITUTE(A1,CHAR(160),"").
The problem with this was that the converted numbers were not working in more complicated formulas.
Is there any other (simple & not formula based) way to transform this text to simple numbers that can be used for simple calculations???


My example below:

• First I copy paste text from web page in this format

[TABLE="class: s1w8oh2o-19 fLUOnB"]
<tbody>[TR="class: s1w8oh2o-20 lglzjB"]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"][/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"]A[/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"][/TH]
[/TR]
[TR="class: s1w8oh2o-20 lglzjB"]
[TD="class: s1w8oh2o-21 dIcMCi"]1[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"]1:3[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"][/TD]
[/TR]
</tbody>[/TABLE]
2. I use the "Text to column" function to get each number in separate cell.


[TABLE="class: s1w8oh2o-19 fLUOnB"]
<tbody>[TR="class: s1w8oh2o-20 lglzjB"]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"][/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"]A[/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"]B[/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"]C[/TH]
[/TR]
[TR="class: s1w8oh2o-20 lglzjB"]
[TD="class: s1w8oh2o-21 dIcMCi"]1[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"]1:3[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"]1[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"]3[/TD]
[/TR]
</tbody>[/TABLE]
3. I use this formula =0+SUBSTITUTE(A1,CHAR(160),"") to transform the text to numbers.


[TABLE="class: s1w8oh2o-19 fLUOnB"]
<tbody>[TR="class: s1w8oh2o-20 lglzjB"]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"][/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"]A[/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"]B[/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"]C[/TH]
[TH="class: s1w8oh2o-25 ieLkgp, align: center"]D[/TH]
[/TR]
[TR="class: s1w8oh2o-20 lglzjB"]
[TD="class: s1w8oh2o-21 dIcMCi"]1[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"]1:3[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"]1[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"]3[/TD]
[TD="class: s1w8oh2o-21 dIcMCi"]=0+SUBSTITUTE(A1,CHAR(160),"")[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for all suggestions and help !!!

 

[steve the fish]

If the substitute formula doesnt error than that is producing a real number. There is no reason that number should not work in more complicated formulas.

 

[bobsan42]

or try to use VALUE function
But Steve the fish is right - if you don't get an error in column D then it is a number (in this case 0.333333(3) )
But if the numbers in B and C don't work - use Value() for them.