Stripping Middle Initial from a Name

[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!

 

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

 

[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.

 

[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!

 

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

 

[mira]

Bob, the formula below stripped off everything after the last name. Help!

 

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

 

[Duke Carey]

maybe

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

Bob, the formula below stripped off everything after the last name. Help!

 

[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!
==============================================

 

[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

 

[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

 

[Bob Phillips]

Gosh, there's a name from the past!

Bob

RagDyer wrote...
...

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

Thant, U

 

[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.

 

[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.