Stripping Middle Initial from a Name

M

mira

Hello.

I need a function that strips the Middle Initial of a name. The names on my
spreadsheet are all mixed up with names that have middle initials, and some
that done. For example, I would like to strip the middle initial, 'X' off
this name, "Doe, John, X", where Doe=last name, John=First Name, X=Middle
Initial

Hope that makes sense.

Thanks!
 
B

Bob Phillips

Mira,

Try this

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))=2,LEFT(A1,FIND(",",A1,FIND(",",A1)+1)
-1),A1)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

I'd use a helper cell:

=IF(MID(A1,LEN(A1)-2,2)=", ",LEFT(A1,LEN(A1)-3),A1)

And drag down the column.
 
M

mira

Hi, Dave.

Sorry! I made a mistake. The format of the name is Lastname, FirstName X
where X=middle initial. I mistakenly put a comma after Firstname. I tried
modifying your formula, but it doesn't seem to be working for me. =) Can
you help?

Thanks!
 
B

Bob Phillips

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,LEFT(A1,FIND(" ",A1,FIND("
",A1)+1)-1),A1)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
H

Harlan Grove

mira wrote...
Sorry! I made a mistake. The format of the name is Lastname, FirstName X
where X=middle initial. I mistakenly put a comma after Firstname. I tried
modifying your formula, but it doesn't seem to be working for me. =) Can
you help?
....

If the middle initial would always be the last nonspace character and
would always be preceded by a space, you could try

=IF(AND(LEFT(RIGHT(TRIM(x),3),1)<>",",LEFT(RIGHT(TRIM(x),2),1)=" "),
LEFT(TRIM(x),LEN(TRIM(x))-2),TRIM(x))

The TRIM calls guard against leading, trailing and multiple sequential
spaces in x. If there were no extra spaces and x were a cell reference,
you could use

=IF(COUNTIF(x,"*,?* ?"),LEFT(H1,LEN(H1)-2),H1)
 
R

RagDyer

See if this works for you:

=IF(CODE(RIGHT(A1,2))=32,LEFT(A1,LEN(A1)-2),A1)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
H

Harlan Grove

RagDyer wrote...
See if this works for you:

=IF(CODE(RIGHT(A1,2))=32,LEFT(A1,LEN(A1)-2),A1)
....

This could screw up some Asian names, e.g.,

Thant, U
 
R

RagDyeR

Yes, I realize that Harlan.

There are so many, that it's difficult to count them all, as they ride by on
their zebras.<g>

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

RagDyer wrote...
See if this works for you:

=IF(CODE(RIGHT(A1,2))=32,LEFT(A1,LEN(A1)-2),A1)
....

This could screw up some Asian names, e.g.,

Thant, U
 
H

Harlan Grove

RagDyeR wrote...
Yes, I realize that Harlan.

There are so many, that it's difficult to count them all, as they ride by on
their zebras.<g>
....

Flippancy aside, what distinguishes a middle name is that it's a final
nonspace character preceded by a space character which in turn is *not*
preceded by a comma. Don't get lazy and forget to check for a comma as
3rd character from the end of the string.
 
R

RagDyeR

Flippancy aside Harlan, you pay me too much credit, assuming I was "lazy" to
omit the check for the coma preceding the space.

Honestly, I just didn't think about it.

As always, I become slightly more enlightened by each of our interchanges,
be they confrontational OR flippant:

=IF(AND(CODE(RIGHT(A1,2))=32,CODE(RIGHT(A1,3))<>44),LEFT(A1,LEN(A1)-2),A1)
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------


RagDyeR wrote...
Yes, I realize that Harlan.

There are so many, that it's difficult to count them all, as they ride by on
their zebras.<g>
....

Flippancy aside, what distinguishes a middle name is that it's a final
nonspace character preceded by a space character which in turn is *not*
preceded by a comma. Don't get lazy and forget to check for a comma as
3rd character from the end of the string.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top