Sorting Numbers in EXCEL

[postmanphat]

Hi, I hope someone can help me cos I'm tearing my hair out here.

This is so simple its untrue: I've got a list of about 1200 6 digit numbers. I want to put them in ascending order. But Excel simply won't do it. You sort it and it looks fine, then you get a certain way down the list and its all muddled. I've tried everything I can think of - copying it into other sheets, reformatting the cells as currency,number, everything. I've even tried importing it into Access but it won't even import half the numbers across (presumably the numbers that aren't being sorted in Excel)

Somebody please help before I start smashing stuff up

Thanks in advance.

 

[AnotherHiggins]

They are either formatted as text and you want them to be formatted as numbers,
[tab]or
they are formatted as numbers and you want them to be formatted as text.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Doroth]

I've seen this happen when there is an empty space before the number. Perhaps this is the issue? Click in one of the cells that is problematic to see if there is a space that is creating the problem.

 

[SkipVought]

Hi,

Chances are, if I were a betting man, I'd wager that your list of 6-digit numbers, are really IDENTIFIERS, like InvoiceID or EmployeeID, or PartID; some identifier that you would NEVER do arithmetic on.

Identifiers, be they all numeric or not, ought to be stored as TEXT. Numbers and Text are different animals, and computers hand them in a vastly different way. Hence your dilemma.

Formatting changes NOTHING (ie Format > Cells ...) It is only for display purposes. You must change the underlying values. You can accomplish that by prefixing a single QUOTE...
[tt]
="'" & A1
[/tt]
where A1 is a cell containing one of your 6-digit numbers.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Welshbird]

The other way to change the probably text to a number is in a formula in an adjacent cell
=value(A2)

Copy that all the way down and then sort on this new column.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[postmanphat]

Cheers erverybody.

I found that copying my list of 'numbers' into notepad, then copying them back into Excel THEN sorting them fixed my problem.

Thanks

 

[dianemarie]

Another quick solution is to key the number 1 into a cell you know to be a general format. Then highlight the column of numbers/text that is giving you trouble, and do a Paste Special - Multiply. This tricks Excel into seeing the values as numbers. It's quicker than going into Notepad and back into Excel.