Case sensitive sorting with German Umlauts

[filky]

Hi,

I am trying to sort data in alphabetical order with uppercase, or lowercase (don't care which), taking precedence over the alphabetical sort. So if I have a list like this:

überbieten
Überbeschäftigung
überbesetzt
überaus
Überbezahlung
Übelkeit
überall

I want to sort it like this:

überall
überaus
überbesetzt
überbieten
Übelkeit
Überbeschäftigung
Überbezahlung

I tried the case sensitive sort in the Data tab, Sort & Filter Group, but it won't pay any attention to words starting with an umlaut like this. In fact it doesn't seem to pay any attention to case at all, whether I use an umlaut or not. Does anyone have any ideas how to handle this? I'm using MS Office Professional Plus 2013.

Thank you

Paul

[Ace_XL]

A bit of a workaround..Try this

- Select your list
- Find & replace (Ctrl+H) -- Find ü -- Replace 1 -- Check the 'Match case' checkbox -- Replace All
- Sort your list (ascending or descending)
- Find & Replace (Ctrl+H) -- Find 1 -- Replace ü -- Replace All

Not ideal, but hope that helps

[shg]

UDF?

Row\Col	A	B	C
1	Input	Sort
2	überbieten	FC62657262696574656E	B2: =Str2Asc(A2)
3	Überbeschäftigung	DC6265726265736368E466746967756E67
4	überbesetzt	FC62657262657365747A74
5	überaus	FC626572617573
6	Überbezahlung	DC62657262657A61686C756E67
7	Übelkeit	DC62656C6B656974
8	überall	FC626572616C6C

Sort by the Sort column:

Row\Col	A	B
1	Input	Sort
2	Übelkeit	DC62656C6B656974
3	Überbeschäftigung	DC6265726265736368E466746967756E67
4	Überbezahlung	DC62657262657A61686C756E67
5	überall	FC626572616C6C
6	überaus	FC626572617573
7	überbesetzt	FC62657262657365747A74
8	überbieten	FC62657262696574656E

Please Login or Register  to view this content.

[Ace_XL]

In shg's solution, you could use in Cell B2 (if you do not want to pursue the UDF option)

=TRIM(CODE(LEFT(A2))&MID(A2,2,LEN(A2)))

[filky]

Thank you... bit of a workaround but it would help. Good idea.

Thanks

A bit of a workaround..Try this

- Select your list
- Find & replace (Ctrl+H) -- Find ü -- Replace 1 -- Check the 'Match case' checkbox -- Replace All
- Sort your list (ascending or descending)
- Find & Replace (Ctrl+H) -- Find 1 -- Replace ü -- Replace All

Not ideal, but hope that helps

[filky]

UDF?

Thank you... looks interesting but possibly a little tricky for my usecase.

[filky]

In shg's solution, you could use in Cell B2 (if you do not want to pursue the UDF option)

=TRIM(CODE(LEFT(A2))&MID(A2,2,LEN(A2)))

In shg's solution, you could use in Cell B2 (if you do not want to pursue the UDF option)

=TRIM(CODE(LEFT(A2))&MID(A2,2,LEN(A2)))

Hi, this was interesting. But I didn't give you the full picture. The words in my list are scattered all over, so with this method I'd like to apply the formulae against every word and then sort the column. But this won't work as the codes generated by the diacritics are not sequential with the rest. So I get this kind of result which messes with the sorting.

r 114
s 115
t 116
u 117
ü 252
Ü 220
v 118
w 119
x 120

I suppose I could do a double sort and group all the ü's and Ü's together and then just sort these. But your first idea would probably be easier. I'm very surprised to find this problem in the first place. But you have all given me some good ideas.

Thank you for your help. Much appreciated.

Regards

Paul

[shg]

Sorting in Excel is surprisingly nuanced.

If you wanted a particular sort order (including case) that Excel doesn't support, you could pass that as a string to a UDF and get exactly what you want.

[filky]

Sorting in Excel is surprisingly nuanced.

If you wanted a particular sort order (including case) that Excel doesn't support, you could pass that as a string to a UDF and get exactly what you want.

Thanks SHG. I have run your code and it does a fantastic job on the umlauts. But doesn't handle other letters correctly and I have no idea how to adapt the code to fix this. So I could use this on the umlauts only and case sensitive differences, but not on the whole column for all letters.

For example, here the "a" and "w" are moved to the end or the start depending on which way you sort... but the values created don't allow sorting as required:

German	English	Sort
überängstlich	overanxious	FC626572E46E6773746C696368
überblättern	skip over	FC626572626CE4747465726E
Übeltäter	culprit	DC62656C74E4746572
Übelsein	nausea	DC62656C7365696E
Übelkeit	nausea	DC62656C6B656974
wasser	water	776173736572
apfel	apple	617066656

Clever idea though.

Thank you

Paul

[filky]

Sorting in Excel is surprisingly nuanced.

If you wanted a particular sort order (including case) that Excel doesn't support, you could pass that as a string to a UDF and get exactly what you want.

Thanks SHG. I have run your code and it does a fantastic job on the umlauts. But doesn't handle other letters correctly and I have no idea how to adapt the code to fix this. So I could use this on the umlauts only and case sensitive differences, but not on the whole column for all letters.

For example, here the "a" and "w" are moved to the end or the start depending on which way you sort... but the values created don't allow sorting as required:

German	English	Sort
überängstlich	overanxious	FC626572E46E6773746C696368
überblättern	skip over	FC626572626CE4747465726E
Übeltäter	culprit	DC62656C74E4746572
Übelsein	nausea	DC62656C7365696E
Übelkeit	nausea	DC62656C6B656974
wasser	water	776173736572
apfel	apple	617066656

Clever idea though.

Thank you

Paul