Formatting Data Pasted from Web Based Table for Summation

[Guest]

I have cut data out of a web based table and pasted into excel. I then try
to sum a column of this data but it will not sum. I checked the text format
and their don't seem to be any issues. Note that I can cut this data from
the web page and paste into word, then copy from word and "paste special"
into excel and then summing works. I'm trying to aviod these intermediate
steps. Your help would be appreciated.

 

[Dave O]

I notice when I copy and paste from a website into Excel that a lot of
"overhead" from the website carries over into Excel: colors, fonts,
spaces, tabs, etc. Even if your cell is formatted as a number if the
figure comes from the web page as a text string Excel will still treat
it as text. A leading apostrophe, leading or trailing space, even
sometimes a $ sign will cause Excel to interpret the string as text.

Try this macro on a backed up copy of your file. Highlight the range
of values in question and run this code; it copies the formula value
into memory, deletes it, reformats the cell as Number format with a
comma and two decimal places, then re-writes the value into the cell.
If any text entries persist they will be left-justified in the cell,
and you'll see they need additional attention; numeric entries will be
right-justified.

Sub Selected_Range_Format()
Dim rCell As Range
Dim TrueVal As Variant

For Each rCell In Selection.Cells
TrueVal = Trim(rCell.Value)
rCell.ClearContents
rCell.HorizontalAlignment = xlGeneral
rCell.NumberFormat = "#,##0.00"
rCell.Value = TrueVal
Next rCell

End Sub