Stripping Middle Initial from a Name

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
>

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" <[email protected]> wrote in message
news:[email protected]...
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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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(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)

=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" <[email protected]> wrote in message
news:[email protected]...
> 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...
>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.

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > >
> >
> >
> >

Gosh, there's a name from the past!

Bob


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
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

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,

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)


"mira" <[email protected]> wrote in message
news:[email protected]...
> 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!

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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(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)

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >

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" <[email protected]> wrote in message
news:[email protected]...
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.

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
>

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" <[email protected]> wrote in message
news:[email protected]...
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

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

Gosh, there's a name from the past!

Bob


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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)


"mira" <[email protected]> wrote in message
news:[email protected]...
> 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!

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.

=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" <[email protected]> wrote in message
news:[email protected]...
> 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,

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)


"mira" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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...
>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.

Gosh, there's a name from the past!

Bob


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
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...
>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

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" <[email protected]> wrote in message
news:[email protected]...
> 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(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)

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > >
> >
> >
> >

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,

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)


"mira" <[email protected]> wrote in message
news:[email protected]...
> 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!

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >

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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > >
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
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.

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)

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >

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
>

Gosh, there's a name from the past!

Bob


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
news:[email protected]...
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

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" <[email protected]> wrote in message
news:[email protected]...
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.

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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

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)

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >

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
>

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,

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)


"mira" <[email protected]> wrote in message
news:[email protected]...
> 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,FIND(",",A1)+1)
-1),A1)

--

HTH

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


"mira" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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(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)

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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

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!

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" <[email protected]> wrote in message
news:[email protected]...
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
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.