Need Help With Writing Concatenate Function
 

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.

Need Help With Writing Concatenate Function
 

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" wrote:

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.

Need Help With Writing Concatenate Function
 

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" skrev i meddelelsen
...
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.

Need Help With Writing Concatenate Function
 

Thanks for your help.

"Tom Hutchins" wrote in message
...
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" wrote:

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.

Need Help With Writing Concatenate Function
 

Thanks for your help.

"Per Jessen" wrote in message
...
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" skrev i meddelelsen
...
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.

Need Help With Writing Concatenate Function
 

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"))

--
Rick (MVP - Excel)


"Michael Styles" wrote in message
...
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.