Character sort order ?

[Bruce Sinclair]

I have been trying to make sense out of how XL sorts my text. The help file
is clear on this ... and yet it sorts "[" before "1" (which is not the
stated default order).

So ... apart from special defined lists, is there an option to turn this
default order off ... or to change it in some way ?
... or am I missing something really simple here ?

Thanks


Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Debra Dalgleish]

Perhaps the 1 is text, not a number, so it's sorted after the symbols.

I have been trying to make sense out of how XL sorts my text. The help file
is clear on this ... and yet it sorts "[" before "1" (which is not the
stated default order).

So ... apart from special defined lists, is there an option to turn this
default order off ... or to change it in some way ?
.. or am I missing something really simple here ?

 

[Pete_UK]

Are you sure that the "1" really is text and not a numeric value of 1?
If it is text then it should sort the 1 before [ based on the character
code, but if it is a number then it will be sorted after.

Hope this helps.

Pete

 

[Bruce Sinclair]

I have been trying to make sense out of how XL sorts my text. The help file
is clear on this ... and yet it sorts "[" before "1" (which is not the
stated default order).

So ... apart from special defined lists, is there an option to turn this
default order off ... or to change it in some way ?
.. or am I missing something really simple here ?

Perhaps the 1 is text, not a number, so it's sorted after the symbols.

They show as "general" when I check the formats. It is text that XL says it
is sorting in a particular way in the help file.
Is there a particular way that XL treats "general" formats ? There are no
'number only' cells in the sorted range ... but if it treats cells beginning
with numbers differently, that might explain it.

I did have difficulty a while back with mail merge using XL data that
started with numbers. I had to move the digits to the end of the cell before
it worked - could this be related to that problem perhaps ? ... it is a
very different file

Thanks




Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Bruce Sinclair]

Are you sure that the "1" really is text and not a numeric value of 1?
If it is text then it should sort the 1 before [ based on the character
code, but if it is a number then it will be sorted after.

[1,2-14C] Acetic Acid, Sodium salt
1-14C Linoleic Acid
2-Deoxy_D-Ribose

These are the first 3 cells. They all show as "general" when I check the
formatting. From XL's stated text order, they should be

1-14C Linoleic Acid
2-Deoxy_D-Ribose
[1,2-14C] Acetic Acid, Sodium salt

Thanks


Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Debra Dalgleish]

In the example you posted in your reply to Pete:

[1,2-14C] Acetic Acid, Sodium salt
1-14C Linoleic Acid
2-Deoxy_D-Ribose

all the entries are text, because even though they start with numbers,
they contain other characters. The cell's formatting won't have any
effect on the sort order.

If you had a cell that contained a number, and no other characters, it
would be sorted before all the examples you listed.

I have been trying to make sense out of how XL sorts my text. The help file
is clear on this ... and yet it sorts "[" before "1" (which is not the
stated default order).

So ... apart from special defined lists, is there an option to turn this
default order off ... or to change it in some way ?
.. or am I missing something really simple here ?

Perhaps the 1 is text, not a number, so it's sorted after the symbols.

They show as "general" when I check the formats. It is text that XL says it
is sorting in a particular way in the help file.
Is there a particular way that XL treats "general" formats ? There are no
'number only' cells in the sorted range ... but if it treats cells beginning
with numbers differently, that might explain it.

I did have difficulty a while back with mail merge using XL data that
started with numbers. I had to move the digits to the end of the cell before
it worked - could this be related to that problem perhaps ? ... it is a
very different file

Thanks




Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Bruce Sinclair]

In the example you posted in your reply to Pete:

[1,2-14C] Acetic Acid, Sodium salt
1-14C Linoleic Acid
2-Deoxy_D-Ribose

all the entries are text, because even though they start with numbers,
they contain other characters. The cell's formatting won't have any
effect on the sort order.

If you had a cell that contained a number, and no other characters, it
would be sorted before all the examples you listed.

It's all text - agreed. The problem is, that the help file says that excel
sorts TEXT according to the character order specified ... and yet, it
doesn't seem to do so. It says clearly "text and text that includes numbers"
are sorted in the order specified (which should place the [ after any
digits).

I'm still confused here and any further pointers as to why this might be
happening would be welcome.

Thanks




Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Debra Dalgleish]

I think that list in Help shows the order when text and numbers are
mixed ("In an ascending sort, Microsoft Excel uses the following order.")

When the numbers are mixed with text, they follow the symbols, as you're
seeing in your worksheet.

In the example you posted in your reply to Pete:

[1,2-14C] Acetic Acid, Sodium salt
1-14C Linoleic Acid
2-Deoxy_D-Ribose

all the entries are text, because even though they start with numbers,
they contain other characters. The cell's formatting won't have any
effect on the sort order.

If you had a cell that contained a number, and no other characters, it
would be sorted before all the examples you listed.

It's all text - agreed. The problem is, that the help file says that excel
sorts TEXT according to the character order specified ... and yet, it
doesn't seem to do so. It says clearly "text and text that includes numbers"
are sorted in the order specified (which should place the [ after any
digits).

I'm still confused here and any further pointers as to why this might be
happening would be welcome.

Thanks




Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Bruce Sinclair]

I think that list in Help shows the order when text and numbers are
mixed ("In an ascending sort, Microsoft Excel uses the following order.")

When the numbers are mixed with text, they follow the symbols, as you're
seeing in your worksheet.

This is what I'm thinking. The help is wrong.
When I look at the characters generated, the digits should indeed preceed
the [. Only other possibility is that I have some of the non visible
characters leading in the cells - and I don't believe that.

Thanks

In the example you posted in your reply to Pete:

[1,2-14C] Acetic Acid, Sodium salt
1-14C Linoleic Acid
2-Deoxy_D-Ribose

all the entries are text, because even though they start with numbers,
they contain other characters. The cell's formatting won't have any
effect on the sort order.

If you had a cell that contained a number, and no other characters, it
would be sorted before all the examples you listed.

It's all text - agreed. The problem is, that the help file says that excel
sorts TEXT according to the character order specified ... and yet, it
doesn't seem to do so. It says clearly "text and text that includes numbers"
are sorted in the order specified (which should place the [ after any
digits).

I'm still confused here and any further pointers as to why this might be
happening would be welcome.

Thanks




Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Roger Govier]

Hi Bruce

You could "cheat" to achieve your desired (correct) result.
In another helper column, which can be hidden if required, enter
=SUBSTITUTE(A1,"[","9")
and then sort by this helper column.
I am not a chemist, and I am assuming you don't have other chemicals
beginning with 9,

--
Regards

Roger Govier

I think that list in Help shows the order when text and numbers are
mixed ("In an ascending sort, Microsoft Excel uses the following
order.")

When the numbers are mixed with text, they follow the symbols, as
you're seeing in your worksheet.

This is what I'm thinking. The help is wrong.
When I look at the characters generated, the digits should indeed
preceed
the [. Only other possibility is that I have some of the non visible
characters leading in the cells - and I don't believe that.

Thanks

In the example you posted in your reply to Pete:

[1,2-14C] Acetic Acid, Sodium salt
1-14C Linoleic Acid
2-Deoxy_D-Ribose

all the entries are text, because even though they start with
numbers, they contain other characters. The cell's formatting
won't have any effect on the sort order.

If you had a cell that contained a number, and no other
characters, it would be sorted before all the examples you listed.


It's all text - agreed. The problem is, that the help file says
that excel sorts TEXT according to the character order specified
... and yet, it doesn't seem to do so. It says clearly "text and
text that includes numbers" are sorted in the order specified
(which should place the [ after any digits).

I'm still confused here and any further pointers as to why this
might be happening would be welcome.

Thanks




Bruce

----------------------------------------
I believe you find life such a problem because you think there are
the good people and the bad people. You're wrong, of course. There
are, always and only, the bad people, but some of them are on
opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

Bruce

----------------------------------------
I believe you find life such a problem because you think there are
the good
people and the bad people. You're wrong, of course. There are, always
and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

 

[Bruce Sinclair]

Hi Bruce

You could "cheat" to achieve your desired (correct) result.
In another helper column, which can be hidden if required, enter
=SUBSTITUTE(A1,"[","9")
and then sort by this helper column.
I am not a chemist, and I am assuming you don't have other chemicals
beginning with 9,

Appreciate the suggestion .. and no, not at this time.
I could use a helper column to get whatever sort order I want (and may do
that when I decide what order that is) ... but I guess I just expect the
help to be accurate.


Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)