I have a column of numbers that were imported from a web site and they won't
sort correctly. I have tried reformatting the cells as general numbers but
that doesn't change anything. I discovered that numbers below 100 have a
space or invisible data in front of the number. If I remove it, the number
then shifts to right alignment in the cell and will sort as a number. Is
there any way I can remove these spaces from all numbers at once instead of
going down the column row by row?

Saved from a previous post.

Extra spaces???
Pasting from a web page??? (Maybe extra non-breaking spaces (HTML stuff))

Cell formatted as text??

If it's the non-breaking spaces stuff, visit David McRitchie's site for a
routine that will clean them up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you still can't find the problem, visit Chip Pearson's site and get his
CellView addin. It should help you check character by character.

http://www.cpearson.com/excel/CellView.htm

FernW wrote:

I have a column of numbers that were imported from a web site and they won't
sort correctly. I have tried reformatting the cells as general numbers but
that doesn't change anything. I discovered that numbers below 100 have a
space or invisible data in front of the number. If I remove it, the number
then shifts to right alignment in the cell and will sort as a number. Is
there any way I can remove these spaces from all numbers at once instead of
going down the column row by row?

--

Dave Peterson