&nbsp in Excel

A

Aaron

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

Thanks in advance
Aaron
 
J

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]
 
D

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Jake Marx said:
Without seeing your HTML file or workbook, this is just a guess. It's [clipped]
Click to expand...
 
Top