alphabet

muppet77

Board Regular
Joined
Jan 24, 2004
Messages
223
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
muppet77 said:
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Derek said:
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Derek said:
.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

Board Regular
Joined
Jan 24, 2004
Messages
223
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
muppet77 said:
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

Board Regular
Joined
Jan 24, 2004
Messages
223
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top