alphabetical categorized

hi

i have a column with customer names for ex.
customer name

Adams john

kite Peterson

jack aloe

what i would like is a second column, that would tell me in increments of 5 what letters it would fit into.
for ex. the first name would fit in category "A-F" the second name would be in the category of "K-O" ETC..

how can i get that done ?

thanks

Your example data is inconsistent - the surname appears first in example 1, but second in the other two examples. Furthermore, the surname begins with a capital letter in the first two examples, but there are no capitals in the final example.

It would help if you attached a sample Excel workbook, so we can see exactly how your data varies.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Ok file uploaded

In this example your categories are only 3 letters wide, i.e. A-C, D-F etc. Also, you seem to have categorised just on the first letter of the name, irrespective of ordering.

Is this what you want?

Pete

Yes this is it

Assuming that is what you want now, you can put this formula in B2:

=IF(A2="","",INDEX($D$2:$D$10,INT((CODE(A2)-65)/3)+1))

then copy down as required.

Hope this helps.

Pete

OK that worked the only question is
is it possible to modify this formula that i shouldn't have to use any reference to any other column but column "A" ?

You can use this formula in B2:

=IF(A2="","",CHOOSE(INT((CODE(UPPER(A2))-65)/3)+1,"A-C","D-F","G-I","J-L","M-O","P-R","S-U","V-X","Y-Z"))

then you don't need to have column D.

Hope this helps.

Pete