Need Help With Writing Concatenate Function

[Michael Styles]

Here is my Data:

A B C D E F
GRADE Teacher LN FN MN DOB
KK Cain FlintstoneFred Bam-Bam02/22/04

I need to concatenate LN, 1st letter of FN, 1st letter of MN, and 1st 4
digits of DOB in MM/DD format.

Can anyone help? All the nesting is not working well in my brain so early
this morning.

Thanks in Advance.

Michael S.

 

[Tom Hutchins]

In another cell in row 2, enter:
=C2&LEFT(D2,1)&LEFT(E2,1)&TEXT(F2,"mm/dd")

To add some error-handling in case any of the fields are empty, tr
=IF(LEN(C2)>0,C2,"x")&IF(LEN(E2)>0,LEFT(E2,1),"x")&IF(LEN(F2)>0,TEXT(F2,"mm/dd"),"xx/xx")

Change "x" to whatever you want returned if the field has no value in it.

Hope this helps,

Hutch

 

[Per Jessen]

Hi

=LEFT(C2,1)&LEFT(D2,1)&LEFT(E2,1)&LEFT(F2,5)

I assume you want 1st 5 digits in DOB.

Regards,
Per

 

[Michael Styles]

Thanks for your help.

In another cell in row 2, enter:
=C2&LEFT(D2,1)&LEFT(E2,1)&TEXT(F2,"mm/dd")

To add some error-handling in case any of the fields are empty, try
=IF(LEN(C2)>0,C2,"x")&IF(LEN(E2)>0,LEFT(E2,1),"x")&IF(LEN(F2)>0,TEXT(F2,"mm/dd"),"xx/xx")

Change "x" to whatever you want returned if the field has no value in it.

Hope this helps,

Hutch

 

[Michael Styles]

Thanks for your help.

Hi

=LEFT(C2,1)&LEFT(D2,1)&LEFT(E2,1)&LEFT(F2,5)

I assume you want 1st 5 digits in DOB.

Regards,
Per

 

[Rick Rothstein]

Assuming your DOB column contains *real* Excel dates (and not just text that
looks like a date)...

=C2&LEFT(D2)&LEFT(E2)&TEXT(F2,"mm/dd")

And this formula will be copied down through blank rows...

=C2&LEFT(D2)&LEFT(E2)&IF(F2="","",TEXT(F2,"mm/dd"))