I am trying to create a formula where I can delete all the characters from the middle name except for the first one. This middle name is as follows in one cell:

Lastname, firstname, middle name. Can I use the upper function to do this-?--the middle names, of course, will be of variable length. With your help I have been able to identify whether the existing middle name is only an intial or a longer string.

=IF(EXACT(RIGHT(G2),UPPER(RIGHT(G2))),G2,"Need to figure this out still")

Thanks again for your help.
-Beverly

See if this works for you:

=LEFT(A1,FIND(",",A1,FIND(",",A1)+1))&" "&MID(A1,FIND
("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE
(A1," ",""))))+1,1)&"."

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I am trying to create a formula where I can delete all
the characters from the middle name except for the first
one. This middle name is as follows in one cell:
>
>Lastname, firstname, middle name. Can I use the upper
function to do this-?--the middle names, of course, will
be of variable length. With your help I have been able to
identify whether the existing middle name is only an
intial or a longer string.
>
>=IF(EXACT(RIGHT(G2),UPPER(RIGHT(G2))),G2,"Need to figure
this out still")
>
>Thanks again for your help.
>-Beverly
>.
>

Hi Jason-
Thanks so much. I haven't quite gotten it to work yet----can you tell me why you use the "#"--oh maybe it's the number as the result of the "find" function. I will keep struggling with this--Thanks for giving me something to work with.

----- Jason Morin wrote: -----

See if this works for you:

=LEFT(A1,FIND(",",A1,FIND(",",A1)+1))&" "&MID(A1,FIND
("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE
(A1," ",""))))+1,1)&"."

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I am trying to create a formula where I can delete all
the characters from the middle name except for the first
one. This middle name is as follows in one cell:
>>Lastname, firstname, middle name. Can I use the upper
function to do this-?--the middle names, of course, will
be of variable length. With your help I have been able to
identify whether the existing middle name is only an
intial or a longer string.
>>=IF(EXACT(RIGHT(G2),UPPER(RIGHT(G2))),G2,"Need to figure
this out still")
>>Thanks again for your help.
>-Beverly
>.
>

Jason-
Your formula works fine. My problem is that I told you that there was a comma after the first name (which there is not)--so I am working to adjust what you gave me.

lastname, firstname midinitial
Thanks
-Beverly

This should work:

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1))&" "&MID(A1,FIND
("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE
(A1," ",""))))+1,1)&"."

Be aware that this will fail with names containing more
than 3 words such as De la Hoya, Oscar Benito.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Jason-
>Your formula works fine. My problem is that I told you
that there was a comma after the first name (which there
is not)--so I am working to adjust what you gave me.
>
>lastname, firstname midinitial
>Thanks
>-Beverly
>.
>