alphabet

[muppet77]

whats the best way to get a value for a particular text cell , so that it ranks it in a list of cells, based on alphabetical order?

eg if i have cells a d c b, i want to get the correct ranks:

a 1
d 4
c 3
b 2

 

[P Sitaram]

Try:

=CODE(A1)-64

 

[Aladin Akyurek]

whats the best way to get a value for a particular text cell , so that it ranks it in a list of cells, based on alphabetical order?

eg if i have cells a d c b, i want to get the correct ranks:

a 1
d 4
c 3
b 2

If you had one more "a", what ranking do you want to assign to each "a" ?

 

[Derek]

Hi there

To add to Sitaram's excellent answer, you might like to ensure it reads the letters as capitals with this modification:


=CODE(UPPER(A1))-64

regards
Derek

 

[Aladin Akyurek]

Hi there

To add to Sitaram's excellent answer, you might like to ensure it reads the letters as capitals with this modification:


=CODE(UPPER(A1))-64

regards
Derek

What if "a", "b",... were just example values and the real data consists of items of any length?

 

[Derek]

Ah yes Aladin, a much more complex problem, as usual you are many steps ahead of me. Wonder what the answer is?
kind regards
Derek
PS If pushed I would probably try sorting the column alphabetically and then ranking it

 

[Aladin Akyurek]

.PS If pushed I would probably try sorting the column alphabetically and then ranking it

Yes, a non-manual sorting might be part of what is needed. Hope to propose a solution along those lines after the question I posted is answered.

 

[muppet77]

cheers chaps.
i should have actually said that the info was longer texts

apple
banana
orange
mango
cucumber

trying to avoid using the sort button if at all possible.
are we stumped????

 

[Aladin Akyurek]

cheers chaps.
i should have actually said that the info was longer texts

apple
banana
orange
mango
cucumber

trying to avoid using the sort button if at all possible.
are we stumped????

Why don't you also answer the question I posted?

 

[muppet77]

i want to rank the words on the second letter of the word (after the first), then third etc
ie alphabetical order - the title of the thread
is this poss do you think?