Excel question, can you beat FF?

[rick derris]

OK, supposing a school teacher wanted to create 10 character long computer user id's for a new year group of students

all starting with 'C', the 3 columns of data are

first name
last name
initials of form tutor

the 10 digit ID would be

C / two initials of tutor/ first 5 characters of first name/ first 2 characters of last name

if first name less than 5 characters than additional characters taken from surname

we, sorry they, had a simple program to do this but its been lost

[Wyatt Earp]

Excel question, can you beat FF?

How are the first and last names, etc, arranged in the spreadsheet?

[rick derris]

Excel question, can you beat FF?

in their own columns

[Femme Folle]

Excel question, can you beat FF?

"if first name less than 5 characters than additional characters taken from surname"

This bit makes it a bit trickier. Also, do you want the first two initials of the tutor, or the first and last, assuming there are three or more?

You could do it like this:

Column Field
A First Name
B Last Name
C Tutor

=LEFT(C2,2)&(LEFT,A2,5)&(LEFT,B2,2)

[rick derris]

Excel question, can you beat FF?

there are only two letters for the tutor

[rick derris]

Excel question, can you beat FF?

thanks so far BTW

[Femme Folle]

Excel question, can you beat FF?

You're welcome. I'm working on the other issue now. Back in a few.

[Wyatt Earp]

Excel question, can you beat FF?

It matters which columns, though.

Still, let's assume the tutor, first name and last name are in columns A, B and C respectively, and let's start with row 2. In cell D2, type

=UPPER("C"&LEFT(A2,2)&LEFT(B2,5)&LEFT(C2,2))

Then select as many rows in that column as you need, and do ctrl-D.

So

McGonagall Harry Potter

gives

CMCHARRYPO

and

Snape Draco Malfoy

gives

CSNDRACOMA

If I read you right, that's what you want?

[Femme Folle]

Excel question, can you beat FF?

Hey Rick, that's only 9 characters.

edit: Oh, I see...the C is added to the beginning. I thought you were saying all of the names began with C...doh

[Wyatt Earp]

Excel question, can you beat FF?

Oh, wait, you need that additional letters from surname thing. Use

=UPPER("C"&LEFT(A2,2)&LEFT(B2,MIN(5,LEN(B2)))&LEFT (C2,7-MIN(5,LEN(B2))))

[Wyatt Earp]

Excel question, can you beat FF?

I've tested it, and

McGonagall Cho Chung

gives

CMCCHOCHUN

which I think is what you want.

[Femme Folle]

Excel question, can you beat FF?

WE's last formula is the same as the one I came up with.

Well done, WE.

[Wyatt Earp]

Excel question, can you beat FF?

Edited for dimness.

[rick derris]

Excel question, can you beat FF?

nearly there wyatt and FF

at the moment it gives me first 2 letters of first name and 5 letters of first name (want it other way around) and I need tutor to be 2nd and 3rd char

looking at it, I am sure I could 'get it' in aboiut an hour. 30 secs for you excel experts

[rick derris]

Excel question, can you beat FF?

the 'C' is to indicate they are year 7 BTW

[Guy Profumo]

Excel question, can you beat FF?

what if there's duplicate userids?

[Guy Profumo]

Excel question, can you beat FF?

Bollocks!

What if there *are* duplicate userids?

[rick derris]

Excel question, can you beat FF?

OK, I have edited it to this:

=UPPER("C"&LEFT(C2, 2)&LEFT(A2,5)&LEFT(B2,MIN(2,LEN(B2)))&LEFT(C2,7-MIN(5,LEN(B2))))

this works, it puts the tutor initials as second and thirs chars, but also as 11 and 12

i undersatnd what is going on in that last nested statemnt but how do i edit it not so put tutor initials 11 and 12

thanks all

Guy, duplicate names of students in same tutor group - very rare

[Wyatt Earp]

Excel question, can you beat FF?

What columns are your data in?

If you want a solution that works out of the bag, you need to tell us this information.

[rick derris]

Excel question, can you beat FF?

sorry wyatt

Forename Surname tutor

[Wyatt Earp]

Excel question, can you beat FF?

Ah! Well then, it's

=UPPER("C"&LEFT(C2,2)&LEFT(A2,MIN(5,LEN(A2)))&LEFT (B2,7-MIN(5,LEN(A2))))

innit.

[Wyatt Earp]

Excel question, can you beat FF?

.

[rick derris]

Excel question, can you beat FF?

brilliant, its only gone and worked!!

Wyatt (& FF) what ever they pay you, it's not enough

[Wyatt Earp]

Excel question, can you beat FF?

No, honestly: not bragging, but this stuff isn't at all hard if you've done any programming.

[WOM]

Excel question, can you beat FF?

Nor is landing a plane very tough if you've done a bit of piloting.