Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 3rd 05, 01:31 PM
FernW
 
Posts: n/a
Default Numbers won't sort correctly.

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?

  #2   Report Post  
Old March 3rd 05, 02:46 PM
Dave Peterson
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Numbers won't sort correctly. FernW Excel Discussion (Misc queries) 10 April 1st 05 01:29 PM
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. [email protected] New Users to Excel 1 February 18th 05 12:59 AM
How to sort random numbers in columns webehere Excel Discussion (Misc queries) 3 January 15th 05 12:24 PM
sort + & - numbers together Rasoul Khoshravan Azar Excel Worksheet Functions 1 December 18th 04 11:11 PM
How do I reference and sort a range of numbers in Excel 97? Old Northern Excel Worksheet Functions 3 December 14th 04 03:15 PM


All times are GMT +1. The time now is 07:22 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017