Two Spaces
There are two types of spaces in Excel. The normal space and one that is called a non-breaking space, often found on websites.
When using some of Excel’s features Excel doesn’t recognise the other type of space. You can use the following formula to convert all non-breaking spaces in a cell to the normal space. Assume the text is cell A1.
=SUBSTITUTE(A1,CHAR(160),CHAR(32))
The CHAR function allows you to refer to ASCII characters by their number. 160 is the non-breaking space, 32 is the normal space.
This blog post discusses the CHAR function in more detail.
Please note: I reserve the right to delete comments that are offensive or off-topic.