Divide students by first letter of last name?

[charlesstricklin]

The school where I teach has two Vice-Principals. All of our 7th grade students go to one or the other based on the first letter of the last name.

Students with first letters of last names beginning with the range A-K go to one Vice-Principal, and the L-Z students goes to the other.

How may I display which one handles each student?

 

[No_Need_For_Macros]

CT,

The formula below checks the universal number associated with the first letter of the last name. This accounts for both upper case and lower case. The "VP1" and "VP2" will be your Vice Principle names. Finally, if the Students last name does not begin with a letter from the English alphabet the formula will return "Not a Letter".

=IF(OR(AND(UNICODE(A1)>=65,UNICODE(A1)<=75),AND(UNICODE(A1)>=97,UNICODE(A1)<=107)),"VP1",IF(OR(AND(UNICODE(A1)>=76,UNICODE(A1)<=90),AND(UNICODE(A1)>=108,UNICODE(A1)<=122)),"VP2","NOT A LETTER"))

 

[fhqwgads]

what does your data look like? are the names all in one column like "John Smith" or "Smith, John" or are first and last names in separate columns?

 

[jtakw]

Hi,

fhqwgads has a valid question, but assuming you have Last Name in a separate cell OR Last Name First Name in the Same cell, you can use this, otherwise, please clarify your data/name format:

Book1
	A	B
1	Name format?
2	Doe, John	VP1
3	smith	VP2
4	Johnson	VP1
5	good, Johnny	VP1
6	Jackson	VP1
7	Roberts	VP2
8	Wilson	VP2
9	Young	VP2
Sheet312

Cell Formulas
Range	Formula
B2	=IF(AND(CODE(UPPER(A2))>=65,CODE(UPPER(A2))<=75),"VP1",IF(AND(CODE(UPPER(A2))>=76,CODE(UPPER(A2))<=90),"VP2",""))

Formula copied down.

 

[charlesstricklin]

Thanks! That's perfect.

 

[Peter_SSs]

Thanks! That's perfect.

What was? - there has been more than 1 suggestion.
What does your data actually look like?
If Last name is listed in a separate cell or first in a combined cell, as far as I can see all you should need is

Excel Workbook
	A	B
2	Doe, John	VP1
3	Smith, Jenny	VP2
4	Johnson, Tim	VP1
5	Good, Johnny	VP1
6	Jackson, James	VP1
7	Roberts, Rob	VP2
8	Wilson, Ann	VP2
9	Young, Kim	VP2
VP

 

[jtakw]

Thanks! That's perfect.

Don't know which you're referring to, but I'd like to amend my formula in Post # 4 to this in case of Blank rows:

Book1
	A	B
1	Name format?
2	Doe, John	VP1
3	smith	VP2
4	# of students
5	Johnson	VP1
6	good, Johnny	VP1
7
8	Jackson	VP1
9	Roberts	VP2
10	% of students
11	Wilson	VP2
12	Young	VP2
Sheet312

Cell Formulas
Range	Formula
B2	=IF(A2="","",IF(AND(CODE(UPPER(A2))>=65,CODE(UPPER(A2))<=75),"VP1",IF(AND(CODE(UPPER(A2))>=76,CODE(UPPER(A2))<=90),"VP2","")))