Sorting by Number

[Big Rich]

I am importing a list based on numbers (jerseys) and when I sort by the
numbers my list comes up like this:
1 Smith, John Martinsvile, IN
10 Jones, Shelly Chicago, IL
11 Martin, Tom St. Petersburg
2 Jelcin, Jeff Grand Rapids, MI
21 Bryant, Kobe Walla, Walla

How do I get them to be 1, 2, 3 and not 1, 10, 11, 2, 21?

 

[Ashish Mathur]

Hi,

Not clear about what you want but I assume you want a continuous range of
numbers. Try this formula =IF(ISERROR(B4+1),1,B4+1). B4 has the heading of
the numbers column. Now even after sorting, the numbers would remain 1,2,3

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Big Rich]

Nope, that didn't work...I'm not even sure that I should need a formula to do
this.

All I am attempting is to do my own Numerical sorting of a sports team's
roster. I've done this before, but now I'm getting the jersey's grouped by
their 10's.

 

[Jacob Skaria]

If you have imorted this may be column with numbers (assume it is Col A) is
in text format. Try this out

--Copy a blank cell
--Keeping the copy select Column A
--Right click>PasteSpecial>select Add>Click OK
--This will convert the data to numerics. Now try sort...

If this post helps click Yes

 

[Bernd P]

Hello,

Your numbers seem to be treated as text by Excel.

Anyway, you can sort them as numbers with
http://sulprobil.com/html/sort_vba.html
for example.

Regards,
Bernd

 

[Charabeuh]

Hello,

Since it is imported text from another application, you got perhaps some
invisible characters like char(160).

You can try :

To sort your list, you could extract the numbers to another column
I supposed your data are in column A
insert a column before the column A
Put the formula below into column A into the first cell at the left of your
data in column B
copy the formula in the first cell down to the last cell

Formula:
=VALUE(LEFT(TRIM(SUBSTITUTE(RC[1],CHAR(160)," ")),FIND("
",TRIM(SUBSTITUTE(RC[1],CHAR(160)," ")))))

The column A should now contains your numbers.
Sort A:B by column A

Excuse me for my awful english !