Excel sort

[user35]

Exported a spreadsheet out of access. Now am trying to sort data.

For some reason will sort
PM-LA
PM-LB
PML-1
PM-N


Almost like it is ignoring the -.
I would think it would do all the PM- together????

 

[SPV]

Works OK for me (Excel 2003) - they order as
PML-1
PM-LA
PM-LB
PM-N

Are all 4 cells formatted the same as each other in your spreadsheet? Mine were all formatted as General.

 

[mintjulep]

PML sorts to either top or bottom of the list for me, depending on ascending or descending.

 

[AnotherHiggins]

Check for any leading spaces (or other unprintable characters)

[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.

 

[user35]

yes, set for General....same thing...This is weird...It's treating the -very weird

 

[SkipVought]

Hi,

Are you sure you don't have some spaces?

SORT...
[tt]
PML-1
PM-LA
PM-LB
PM-N
[/tt]


Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[SPV]

Also, are the hyphens really normal hyphens? Could one or two of them be the long hyphen generated by Word? Copying the selection from Excel into notepad (or other text editor) should show up any differences.

 

[SkipVought]

"set for General" or any NUMBER format, has absolutely NOTHING to do with it.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

PM-LBL-UNSC-3.5
PM-LBL-UNSC-3.5
PML-BTL-1/2 OZ-CLEAR
PML-BTL-1/2 OZ-CLEAR
PML-BTL-1/2 OZ-CLEAR
PML-BTL-1/2 OZ-CLEAR
PM-N-BX-BALS-3.5
PM-N-BX-BALS-3.5
PM-N-BX-BALS-3.5
PM-N-BX-BALS-3.5
PM-N-BX-BLUE-3.5
PM-N-BX-BLUE-3.5
PM-N-BX-BLUE-3.5
PM-N-BX-BLUE-3.5

This is how it is sorting

 

[SkipVought]

which is how it, indeed, should.

What's the point?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

Shouldn't it be printing
PM-
PMA
PMB
PMC

Why is PML between PM-L and PM-N?

 

[SkipVought]

I see, I see. Checking...

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

Just saw this on-line..
Apostrophes and hyphens are ignored unless two cells are identical except for the presence of an apostrophe or hyphen.
For example, Co-ordinator follows Coordinator.

Does excel sort ignore hyphens?

 

[SkipVought]

That is what seems to be happening. I did not know that. I used Edit > Replace to remove the DASH, and that's the way it sorts.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

Either did I.. Very strange...

 

[SkipVought]

Default sort orders
In an ascending sort, Microsoft Excel uses the following order. (In a descending sort, this sort order is reversed except for blank cells, which are always placed last.)

Numbers Numbers are sorted from the smallest negative number to the largest positive number.

Dates Dates are sorted from the earliest date to the latest date.

Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right, character by character. For example, if a cell contains the text "A100," Excel places the cell after a cell that contains the entry "A1" and before a cell that contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[user35]

Thanks everyone... Looks like hyphens are ignored in sorts unless all the other text is identical.. Thought I was losing it... Thanks!!