Sorting by Number

  • Thread starter Thread starter Big Rich
  • Start date Start date
B

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?
 
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
 
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.
 
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
 
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 !
 
Back
Top