Hello,

I have a list of names; some of which contain the fistname, middle initial,
and last name. The other names in the list are only first and last names. For
example: John T. Doe and John Doe. I need to extract the middle intial and
the period after the MI so that I end up with a list of names with just the
first name and last name. I have the formula below that will work
successfully to remove the "MI.":
=LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7))
But when I try to wrap the above formula in an IF statement so that it will
state; "if the name in the cell contains a period then remove it and return
just the FN & LN; if there is no period then just return the name in the
cell. Here is the formula that I have tried to use to accomplish this:
=IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)
Unfortunately I get a #VALUE error with this formula. I feel like I am close
but at this point am stumped on how to get past this error. Any help or
suggestions on how to accomplish my task will be greatly appreciated. Thank
you.

Dave

=IF(FIND(".",B5)0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)


You had the first FIND parameters reversed!

"Dave Y" wrote:

Hello,

I have a list of names; some of which contain the fistname, middle initial,
and last name. The other names in the list are only first and last names. For
example: John T. Doe and John Doe. I need to extract the middle intial and
the period after the MI so that I end up with a list of names with just the
first name and last name. I have the formula below that will work
successfully to remove the "MI.":
=LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7))
But when I try to wrap the above formula in an IF statement so that it will
state; "if the name in the cell contains a period then remove it and return
just the FN & LN; if there is no period then just return the name in the
cell. Here is the formula that I have tried to use to accomplish this:
=IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)
Unfortunately I get a #VALUE error with this formula. I feel like I am close
but at this point am stumped on how to get past this error. Any help or
suggestions on how to accomplish my task will be greatly appreciated. Thank
you.

Dave

FIND returns #VALUE if the text you are looking for is not contained
within the cell. To get round this try this approach:

=IF(ISERROR(FIND(B5,".")),B5,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-
FIND(".",B5)))

Hope this helps.

Pete

On Jul 11, 2:56 pm, Dave Y wrote:
Hello,

I have a list of names; some of which contain the fistname, middle initial,
and last name. The other names in the list are only first and last names. For
example: John T. Doe and John Doe. I need to extract the middle intial and
the period after the MI so that I end up with a list of names with just the
first name and last name. I have the formula below that will work
successfully to remove the "MI.":
=LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7))
But when I try to wrap the above formula in an IF statement so that it will
state; "if the name in the cell contains a period then remove it and return
just the FN & LN; if there is no period then just return the name in the
cell. Here is the formula that I have tried to use to accomplish this:
=IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)
Unfortunately I get a #VALUE error with this formula. I feel like I am close
but at this point am stumped on how to get past this error. Any help or
suggestions on how to accomplish my task will be greatly appreciated. Thank
you.

Dave

Extract the first and last names irrespective of whether there's a middle
name or not:-

=LEFT(A1,FIND(" ",A1)-1)&" "&IF(ISERR(FIND(" ",A1,1+FIND("
",A1))),MID(A1,FIND(" ",A1)+1,LEN(A1)),MID(A1,1+FIND(" ",A1,1+FIND("
",A1)),LEN(A1)))

Mike

"Dave Y" wrote:

Hello,

I have a list of names; some of which contain the fistname, middle initial,
and last name. The other names in the list are only first and last names. For
example: John T. Doe and John Doe. I need to extract the middle intial and
the period after the MI so that I end up with a list of names with just the
first name and last name. I have the formula below that will work
successfully to remove the "MI.":
=LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7))
But when I try to wrap the above formula in an IF statement so that it will
state; "if the name in the cell contains a period then remove it and return
just the FN & LN; if there is no period then just return the name in the
cell. Here is the formula that I have tried to use to accomplish this:
=IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)
Unfortunately I get a #VALUE error with this formula. I feel like I am close
but at this point am stumped on how to get past this error. Any help or
suggestions on how to accomplish my task will be greatly appreciated. Thank
you.

Dave

=IF(ISNUMBER(FIND(".",B5)),REPLACE(B5,FIND(".",B5)-2,3,""),B5)


"Dave Y" wrote:

Hello,

I have a list of names; some of which contain the fistname, middle initial,
and last name. The other names in the list are only first and last names. For
example: John T. Doe and John Doe. I need to extract the middle intial and
the period after the MI so that I end up with a list of names with just the
first name and last name. I have the formula below that will work
successfully to remove the "MI.":
=LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7))
But when I try to wrap the above formula in an IF statement so that it will
state; "if the name in the cell contains a period then remove it and return
just the FN & LN; if there is no period then just return the name in the
cell. Here is the formula that I have tried to use to accomplish this:
=IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)
Unfortunately I get a #VALUE error with this formula. I feel like I am close
but at this point am stumped on how to get past this error. Any help or
suggestions on how to accomplish my task will be greatly appreciated. Thank
you.

Dave

Hello everyone,

I really appreciate your quick responses. I now have the formula working.
You guys are the best! Thank you very much.

"Dave Y" wrote:

Hello,

I have a list of names; some of which contain the fistname, middle initial,
and last name. The other names in the list are only first and last names. For
example: John T. Doe and John Doe. I need to extract the middle intial and
the period after the MI so that I end up with a list of names with just the
first name and last name. I have the formula below that will work
successfully to remove the "MI.":
=LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7))
But when I try to wrap the above formula in an IF statement so that it will
state; "if the name in the cell contains a period then remove it and return
just the FN & LN; if there is no period then just return the name in the
cell. Here is the formula that I have tried to use to accomplish this:
=IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)
Unfortunately I get a #VALUE error with this formula. I feel like I am close
but at this point am stumped on how to get past this error. Any help or
suggestions on how to accomplish my task will be greatly appreciated. Thank
you.

Dave

Extract the first and last names irrespective of whether there's a middle
name or not:-

=LEFT(A1,FIND(" ",A1)-1)&" "&IF(ISERR(FIND(" ",A1,1+FIND("
",A1))),MID(A1,FIND(" ",A1)+1,LEN(A1)),MID(A1,1+FIND(" ",A1,1+FIND("
",A1)),LEN(A1)))

A slightly shorter formula to do the same thing...

=LEFT(A1,FIND(" ",A1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))1,MID(A1,FIND("
",A1,FIND(" ",A1)+1)+1,255),"")

Rick

"Rick Rothstein \(MVP - VB\)" wrote...
....
A slightly shorter formula to do the same thing...

=LEFT(A1,FIND(" ",A1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))1,
MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,255),"")

If A1 contains

John Smith

your formula returns "John ". Methinks it should return the last name
too.

Dave Y wrote...
I have a list of names; some of which contain the fistname, middle
initial, and last name. The other names in the list are only first
and last names. For example: John T. Doe and John Doe. I need to
extract the middle intial and the period after the MI so that I end
up with a list of names with just the first name and last name.
....

As someone who goes by his middle name, I have to question the
'logic' (or lack of same) for doing this. If you did this to my name,
my father's and my grandfather's, we'd all wind up with the same name.
None of us use generational qualifiers since all of us have different
middle names, so there'd be no other way to distinguish between our
names.

You really want to lose the ability to distinguish between possibly
(likely?) different individuals?

If so, try

=IF(COUNT(FIND(" ",TRIM(A1),FIND(" ",TRIM(A1))+1)),
LEFT(TRIM(A1),FIND(" ",TRIM(A1)))&MID(TRIM(A1),
LOOKUP(2,1/(MID(TRIM(A1),seq,1)=" "),seq)+1,255),TRIM(A1))

which uses the defined name seq referring to the formula

=ROW(INDEX(Sheet2!$1:$65536,1,1):INDEX(Sheet2!$1:$ 65536,256,1))

A slightly shorter formula to do the same thing...

=LEFT(A1,FIND(" ",A1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))1,
MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,255),"")

If A1 contains

John Smith

your formula returns "John ". Methinks it should return the last name too

Thanks for spotting that... I copied the wrong formula from my test sheet.
The correct one is..

=LEFT(A1,FIND(" ",A1))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))1,MID(A1,FIND("
",A1,FIND(" ",A1)+1)+1,255),MID(A1,FIND(" ",A1)+1,255))

which, while still shorter than Mike's, it's not as dramatically shorter
than my mis-copying led me to believe it would be. I still think there is a
shorter formula available (just a feeling).

Rick

On Wed, 11 Jul 2007 06:56:06 -0700, Dave Y
wrote:

Hello,

I have a list of names; some of which contain the fistname, middle initial,
and last name. The other names in the list are only first and last names. For
example: John T. Doe and John Doe. I need to extract the middle intial and
the period after the MI so that I end up with a list of names with just the
first name and last name. I have the formula below that will work
successfully to remove the "MI.":
=LEFT(B7,FIND(" ",B7))&RIGHT(B7,LEN(B7)-FIND(".",B7))
But when I try to wrap the above formula in an IF statement so that it will
state; "if the name in the cell contains a period then remove it and return
just the FN & LN; if there is no period then just return the name in the
cell. Here is the formula that I have tried to use to accomplish this:
=IF(FIND(B5,".")0,LEFT(B5,FIND(" ",B5))&RIGHT(B5,LEN(B5)-FIND(".",B5)),B5)
Unfortunately I get a #VALUE error with this formula. I feel like I am close
but at this point am stumped on how to get past this error. Any help or
suggestions on how to accomplish my task will be greatly appreciated. Thank
you.

Dave

This will remove the first instance in the string of a Letter followed by a
Dot.

=IF(ISERR(FIND(".",A1)),A1,TRIM(SUBSTITUTE(
A1,MID(A1,SEARCH("?.",A1),2),"")))

If the individual is using an initial for his first name, and then a full
middle name (e.g. X. John Doe), it will remove the X.

However, with a name like Jesse James Jr., it will return Jesse James J

If these are issues, a UDF would be the simplest way to solve the problem. If
not, perhaps the formula above will work.


--ron