Formatting Data Pasted from Web Based Table for Summation
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
|