Stripping Middle Initial from a Name
 

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!

Mira,

Try this

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

--

HTH

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


"mira" wrote in message
...
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!

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

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!

--

Dave Peterson

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!

"Dave Peterson" wrote:

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

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!

--

Dave Peterson

=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" wrote in message
...
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!

"Dave Peterson" wrote:

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

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!

--

Dave Peterson

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

"Bob Phillips" wrote:

=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" wrote in message
...
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!

"Dave Peterson" wrote:

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

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!

--

Dave Peterson

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(T RIM(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)

maybe

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

"mira" wrote:

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

"Bob Phillips" wrote:

=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" wrote in message
...
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!

"Dave Peterson" wrote:

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

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!

--

Dave Peterson

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



"mira" wrote in message
...
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!

"Dave Peterson" wrote:

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

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!

--

Dave Peterson

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

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

"Harlan Grove" wrote in message
oups.com...
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

Gosh, there's a name from the past!

Bob


"Harlan Grove" wrote in message
oups.com...
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 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.

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


"Harlan Grove" wrote in message
oups.com...
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.