changing alphabetical order in excel

[cura]

I need to change the custom sorting order, but not according to
categories, which is the only way I've found help on. I use a Maltese
font which uses ` / \ etc to represent various characters, and when I
sort a-->z I need excel to sort ie :

a b ` d e f g [ h

... etc

is it possible to change the actual alphabetical order in this way?

 

[Barb Reinhardt]

I'd suggest creating a helper column with the order that you want and sort
on that.

 

[Dana DeLouis]

Hi. Not exactly sure, but see if this will do what you want.
First, make a column of the way you want your data to sort. (ie a b ` d e f
g [ h..etc)

Then, Tools | Options | Custom Lists, and "import" this range.

Now, select your unsorted column of data, and select:
Data | Sort..., and select "Options..."

In the pull-down menu, select your custom sort list.

HTH. :>)

 

[cura]

Hi. Not exactly sure, but see if this will do what you want.
First, make a column of the way you want your data to sort. (ie a b ` d
e f
g [ h..etc)

Then, Tools | Options | Custom Lists, and "import" this range.

Now, select your unsorted column of data, and select:
Data | Sort..., and select "Options..."

In the pull-down menu, select your custom sort list.

HTH. :>)

OK, just tried that and it sorts in the same order as if I hadn't
created a custom sort.

 

[David McRitchie]

Use a helper column. It would be nicer if Excel had a TRANSLATE
(XLAT) function but take a look at
http://www.mvps.org/dmcritchie/excel/sorting.htm#ebcdic
if you use that technique you can create your own substitute
collating sequence with a helper column. That helper column
will be of use only to sorting and look like gibberish.

 

[Earl Kiosterud]

cura,

I don't think a custom list will work for you, since a custom list deals
with words, but not the characters within the words, which is what you're
asking for. I think Barb's suggestion may be the only way (except for a
custom macro that actually does the sorting, instead of Excel's sort). For
the example you give:

a b ` d e f g [ h

The data would have to have to be changed so that it yields c for `. I'm
not sure where to go from there, because I don't know if the c was left out
intentionally.

If I'm right about all this, a macro (a user-defined function you could use
right in the helper column) would likely be the easiest way. I can't
imagine a manageable formula. It would probably be straightforward, using a
list of the characters the way you want the character values ordered, and
would substitute them.

Earl Kiosterud
www.smokeylake.com