Taking the first letter of the first name and last name ...

[CharterJP]

I need a text formula that take the name in cell A2 (John Smith) and a number in cell B2 1234 and combine them in cell c2 JS1234

Essentually taking the First letter of the first and last name and the number and combining them.

Thanks

JP

[DonkeyOte]

Numerous possibilities (pending consistency of name convention of course)

Based on the above:

Please Login or Register  to view this content.

[Maistrye]

Hi,

Try the following:

=UPPER(LEFT(A2,1))&MID(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,1)&B2  

This will only fail if the person's name is a single name. It will work to take the last name, so if you have John Van Dermeer ... it would take JD.

S

[CharterJP]

Thanks, Works great!