&nbsp in Excel

[Guest]

I have copy-pasted a file from HTTP format to excel. The Values will not sum because their are NonBreaking Spaces in the formatting (&nbsp. How can I remove these within Excel

Thanks in advance
Aaron

 

[Jake Marx]

Hi Aaron,

Without seeing your HTML file or workbook, this is just a guess. It's
possible that Excel is treating the values as text strings instead of
numbers. To remedy this, you can do the following:

1) enter 0 in a blank cell
2) copy that cell
3) select the range of cells you want to sum
4) edit, paste special - select Add under Operation

That should force Excel to see those cells as values and not text strings.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[David McRitchie]

Hi Aaron,

See TRIMALL macro in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

If you actually have " " in your cells then include the
following in your macro after the following line:

Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

include the this

Selection.Replace What:=" ", Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="&nbsp", Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Without seeing your HTML file or workbook, this is just a guess. It's [clipped]