Excel 2003 : Alphabetical sorting mixes V and W

[AndieB]

Hi all Excel GURUS,

I need help.

I have Excel 2003 and it is an English version installed version, but my Regional Setting on the Windows XP client is set to Swedish.

In the Swedish language, the letter V comes before the letter W. So if you would sort some words which begins with V and W, all the ones with V will be sorted first and then all the words beginning with W would be sorted.

The issue I have discovered, is that if I select a column which contains the follow words:

Vanish
White
Word
Viper
Who

They don't get sorted like this:
Vanish
Viper
White
Who
Word

Instead, Excel 2003 sorts them like this:
Vanish
White
Who
Viper
Word


How come this is the case??
Any setting somewhere??

Appreciate any kind of help in my issue!

 

[Gerald Higgins]

Wow. I didn't really believe this one, but when I changed my regional setting to Swedish, it did just as you described.

So, can you not change your regional setting to English or something else ?

If that's not an option, what about a helper column using this formula

=CODE(a1)

and then select your words, PLUS the helper column, and sort using the helper column as the first sort key, and the column containing the words as the second sort key.
This worked for me on the small sample you provided, although it is a bit cumbersome.

 

[Michael Simpson]

Can I jump into this thread. I'm seeing a similar (?) problem. I have XP and Excel in English, but when trying to sort text in a column. Office treats the Swedish characters Å, Ä and Ö as A, A and O.

I tried changing my regional settings to Swedish (I assume you mean Excel button, Excel options, Popular "tab", language settings and changed the primary editing language to Swedish). Restarted office but no change in the sort sequence.

Did I misunderstand you or should this be working ?

 

[Gerald Higgins]

I'm really not an expert on this, but what I meant by Regional Settings was the Windows Regional Settings

Windows
Settings
Control Panel
Regional and Language Options
on the Regional Options tab
select some other language

I can't guarantee this will work for you, and it may mess up other things, so be careful !
But it did seem to correct the problem that the OP reported.

 

[Michael Simpson]

Thanks Gerald. That did the trick. I also (obviously) made a note of my previous settings. (Fascinating how many happy amateurs change settings willy-nilly, and then realise that when the new settings don't work, have no idea of what the old settings - that did work - were).

Thanks again

 

[Michael Simpson]

I'm a bit confused here. Changing the Regional settings as you suggested Gerald helped with the sort of the data. I then found and created/tested code from here, but when I run it, all I get is that everything is "English".

Is that because he code there is determining the installation language for Excel (mine is in English) rather than the Windows' regional settings ??

 

[Gerald Higgins]

Michael - sorry, I really don't know the answer to that one.

Anyone else ?