Sorting imported "numbers"

[Guest]

I've imported a long list of "numbers" from a server. Now in my excel table I
can not sort them. I've tried multiplying the col. by "1" from paste special,
Add from a free cell and paste special, using a macro form the converting
text to number page, using trim and clean function, copy and paste as value,
using the text to columns command, all to no avail. The "numbers" still stay
left justified and sort by first number, i.e. 113 before 21 etc.

There are 1500 entries and I do no think the table woudl be of much use
after i tried to re-type in all the numbers. The three numbers I did have to
enter do work fine, and sort above all the other "numbers" . I have several
columns that have "numbers" in them and woudl like to be able to sort, at
varoius time, by any one.

 

[Ken Russell]

You need leading zeros to sort correctly. eg. 21 should be 021 if your
largest number contains three digits.

--
Ken Russell

(e-mail address removed)
Remove yourhat to reply by e-mail
..

 

[Guest]

All the numbers are 6 digits long. There are a few that are 5 digit and they
stay at the bottom when sorted because of thier higher first number. However,
I did cut and paste all the remaining 6 digit numbers in a different column
and applied the function I mentioned above, I got the same result e.g. sorted
by first number.

 

[CLR]

Another trick is to copy all over BY FORMULA to another column, such as

in B1 put =A1 and copy down..................then try reformatting column B
to numbers

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Maybe you have a leading space in front of each number...............try

TRIM(a1)

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

I did the trim function. I finally fixed it by doing a "save as" a csv.
(comma delimited text file) I opened that file in notepad and did a find and
replace of all the spaces after the numbers, e.g. changed ,410339 ,xxx, to
,410339,xxx,. This changed all the text/numbers to real numbers and it sorted
fine after I opened the csv file in excel. I saved it as a excel fiel and now
all is well. I am sure there are easier ways to do this.