Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting Data Pasted from Web Based Table for Summation
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting data in a table from excel export | Excel Discussion (Misc queries) | |||
Help! data from pasted table is not included in sum | Excel Worksheet Functions | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Conditional formatting in a data table | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel |