# Excell Formula

KW2K 10:21 12 Jul 2008
Locked

Hi !
I wonder if anyone knows the formula to carry out the following: I have a spreadsheet with three colums, the first one has a list of surnames, the second column has a list of first names and in the third column I want to have Surname followed by the first letter of the Firstname followed by 08, ie BrownC08. Where Brown is the Surname, C is the first letter of the Firstname and 08 is 2008. I would also like to know how to change the first letter of the surname and first letter of the firstname to Capital Latters.

tasslehoff burrfoot 10:39 12 Jul 2008

I don;t have excel so can't check if this is exactly right but something like

=A1&(left(b1, 1))&"08"

maybe, I suspect there's something wrong with the bracketing in that though?

I'm not sure about the capitalisation incell, but you could hide the original column(s) and have another column with =proper(A1)

VoG II 10:40 12 Jul 2008

with

A1 = brown

B1 = colin

and assuming that you want to append the current year:

=UPPER(LEFT(A1,1))&RIGHT(A1,LEN(A1)-1)&UPPER(LEFT(B1,1))&TEXT(TODAY(),"yy")

KW2K 10:54 12 Jul 2008

Hi VoG, Your formula works perfect, just what I was looking for ! One last question though, by doing this I have created a column with a list of user names from two columns with Surname and Firstname. I notice that in my newly created column I have got some duplicate names, ie John Brown gives BrownJ08 & Johanne Brown gives BrownJ08. Is there any easy way to search for duplicate names and where it finds them to add a 1 or 2 to the end of the newly created name, ie BrownJ081 & BrownJ082.

Thanks Once Again !

VoG II 11:26 12 Jul 2008

I don't know of a way of doing this in a single formula without creating a circular reference. However you can do it using a helper column which can be hidden. So

A1 = brown
B1 = colin
C1 = =UPPER(LEFT(A1,1))&RIGHT(A1,LEN(A1)-1)&UPPER(LEFT(B1,1))&TEXT(TODAY(),"yy")
D1 = =COUNTIF(C\$1:C1,C1)
E1 = =C1&D1

and formulas filled down.

KW2K 14:24 12 Jul 2008

Hi VoG,
Again, your suggestion is spot on, that will do all that I require. Thanks once again for your help in resolving this matter, it's much appreciated !

Kind Regards.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Comment afficher des fichiers cachés sur Mac ?