Non-breaking space in Excel

[benjamenus]

Anyone know the correct way to find/replace a non-breaking space in Excel? In Word, the code is ^s ... but Excel doesn't seem to approve?!

Bit of background info ...

Was sent an excel sheet with columns of numbers but was not able to perform any calculations on them (e.g. SUM, etc) even though the cells were formatted as numbers. I noticed the values were all followed by a space, but find/replace didn't remove them. By pasting the column into Word, I discovered that they were all followed by a non-breaking space. Back in excel, I tried a find/replace on ^s (non-breaking space), but to no avail. I discovered that by selecting the cell and copying the 'space' from the formula bar - that I could perform the find/replace with this character (which shows only as a space). I recorded this in a macro and the code also shows the search item as " " (a space). Is there a correct way to identify a non-breaking space in Excel???

P.S. The text was copied from a web page which is how the character ended up there in the first place

 

[onedtent]

try
=substitue(cell_ref,char(160),"&quot

non-breaking space in Excel is Char(160)

 

[xlbo]

or, in a new column, use
=trim(A1)
where your data starts in A1
or
=clean(a1)

trim should remove spaces
clean should remove non standard characters
try each and see which works for you
HTH Rgds
~Geoff~

 

[DaleWatson123321]

Hi benjamenus,

I'm not certain the following will work, but perpaps it's worth a try.

I created this routine to remove the &quot;forced carriage returns&quot; (hard returns) one gets in Excel but using <Alt> <Enter>.

Sub Remove_Carriage_Returns()
Selection.Replace What:=&quot;&quot; & Chr(10) & &quot;&quot;, Replacement:=&quot; &quot;, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Hope this can help.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[onedtent]

u can also do this:

in find, replace dia.box, type ALT+0160 in the find part and then select replace all

 

[benjamenus]

thanks folks - all solutions did the trick of removing the character ... EXCEPT ... the CLEAN/TRIM/SUBSTITUTE functions don't allow you to perform the calculation because excel still treats the value as text (even when the column is number-formatted).

I got round this using =VALUE(substitue(cell_ref,char(160),&quot;&quot)

unfortunately this method doesn't seem to work for CLEAN or TRIM.

Thanks again for your solutions....

P.S. Know any good sites which list ASCII codes - the one I use didn't list this particular one, and it would've been rather helpful.