Converting middle name to middle initial

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
Click to expand...
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
Click to expand...
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.
 
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
Click to expand...
the characters from the middle name except for the first
one. This middle name is as follows in one cell: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.
 
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
Click to expand...
that there was a comma after the first name (which there
is not)--so I am working to adjust what you gave me.
 
Back
Top