Remove middle initial from "first name middle initial"

[Justin F.]

Hello Excel Gurus,

I've been searching the site for an answer to this question but as yet no
luck. If I missed a previous post on this topic I apologize, please direct me
to it.

I have 1 column with a series of people's first names. Many have a middle
initial, some don't. About half of the cells are of two people's names joined
by an "&". Sample data below:

Frank & Debbie J
Marshall & Teresa
Timothy R
Jeffrey D & Eileen N
Pamela J
James R & Rachel
Brooke S
Myung S
Martin F & Susan A
Kathleen

I need remove the middle initial and preserve the first names and the "&"
with a single space between the names and the "&". Can anyone provide me with
a way to do this?

Many thanks in advance,
Justin F.

[Max]

One way to try ..

Assuming the data is n A2 down,

Put in B2, and copy down:
=IF(OR(ISNUMBER(SEARCH("&",A2)),NOT(ISNUMBER(SEARCH("
",A2)))),A2,LEFT(A2,SEARCH(" ",A2)-1))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Justin F." <Justin [email protected]> wrote in message
news:[email protected]...
> Hello Excel Gurus,
>
> I've been searching the site for an answer to this question but as yet no
> luck. If I missed a previous post on this topic I apologize, please direct
me
> to it.
>
> I have 1 column with a series of people's first names. Many have a middle
> initial, some don't. About half of the cells are of two people's names
joined
> by an "&". Sample data below:
>
> Frank & Debbie J
> Marshall & Teresa
> Timothy R
> Jeffrey D & Eileen N
> Pamela J
> James R & Rachel
> Brooke S
> Myung S
> Martin F & Susan A
> Kathleen
>
> I need remove the middle initial and preserve the first names and the "&"
> with a single space between the names and the "&". Can anyone provide me
with
> a way to do this?
>
> Many thanks in advance,
> Justin F.
>

[Max]

Or, maybe better to wrap TRIM() around the source data in col A,

Put in B2, and copy down:

=IF(OR(ISNUMBER(SEARCH("&",TRIM(A2))),NOT(ISNUMBER(SEARCH("
",TRIM(A2))))),TRIM(A2),LEFT(TRIM(A2),SEARCH(" ",TRIM(A2))-1))

[same formula as before, but with "TRIM(A2)" replacing "A2"]
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Ron Rosenfeld]

On Thu, 23 Jun 2005 18:02:02 -0700, Justin F. <Justin
[email protected]> wrote:

>Hello Excel Gurus,
>
>I've been searching the site for an answer to this question but as yet no
>luck. If I missed a previous post on this topic I apologize, please direct me
>to it.
>
>I have 1 column with a series of people's first names. Many have a middle
>initial, some don't. About half of the cells are of two people's names joined
>by an "&". Sample data below:
>
>Frank & Debbie J
>Marshall & Teresa
>Timothy R
>Jeffrey D & Eileen N
>Pamela J
>James R & Rachel
>Brooke S
>Myung S
>Martin F & Susan A
>Kathleen
>
>I need remove the middle initial and preserve the first names and the "&"
>with a single space between the names and the "&". Can anyone provide me with
>a way to do this?
>
>Many thanks in advance,
>Justin F.
>

Assuming your formats are exactly as you show, with only a single space around
the "&" and before and/or after the Initials, then the following formula will
do what you describe on your data set:

=IF(ISERROR(SEARCH(" ? &",IF(ISERROR(SEARCH(" ?",RIGHT(A2,2))),
A2,LEFT(A2,LEN(A2)-2)))),IF(ISERROR(SEARCH(" ?",RIGHT(A2,2))),A2,
LEFT(A2,LEN(A2)-2)),REPLACE(IF(ISERROR(SEARCH(" ?",RIGHT(A2,2))),
A2,LEFT(A2,LEN(A2)-2)),SEARCH(" ? &",IF(ISERROR(SEARCH(" ?",
RIGHT(A2,2))),A2,LEFT(A2,LEN(A2)-2))),3," "))

It returns the following:

Frank & Debbie
Marshall & Teresa
Timothy
Jeffrey & Eileen
Pamela
James & Rachel
Brooke
Myung
Martin & Susan
Kathleen


If there is a concern for extra spaces creeping in, then substitute TRIM(A2)
for the A2's:

=IF(ISERROR(SEARCH(" ? &",IF(ISERROR(SEARCH(" ?",RIGHT(TRIM(A2),2))),
TRIM(A2),LEFT(TRIM(A2),LEN(TRIM(A2))-2)))),IF(ISERROR(SEARCH(
" ?",RIGHT(TRIM(A2),2))),TRIM(A2),LEFT(TRIM(A2),LEN(TRIM(A2))-2)),
REPLACE(IF(ISERROR(SEARCH(" ?",RIGHT(TRIM(A2),2))),TRIM(A2),LEFT(
TRIM(A2),LEN(TRIM(A2))-2)),SEARCH(" ? &",IF(ISERROR(SEARCH(" ?",RIGHT(
TRIM(A2),2))),TRIM(A2),LEFT(TRIM(A2),LEN(TRIM(A2))-2))),3," "))


--ron

[Max]

Superb one, Ron !

My flawed attempt was due to an oversimplified interp <g>
on the spec's part of preserving the data if there's an "&" inside ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

ugh .. pl trash all earlier suggestions here
(mis-read the specs)

See Ron's offering ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Ron Rosenfeld]

On Fri, 24 Jun 2005 10:19:13 +0800, "Max" <[email protected]> wrote:

>Superb one, Ron !
>
>My flawed attempt was due to an oversimplified interp <g>
>on the spec's part of preserving the data if there's an "&" inside ..

I tried yours first, and when it didn't return what I thought the OP wanted, I
decided to work on it.

But I've posted many non-solutions due to misinterpretations of the
specifications, also <sigh>


--ron

[Justin F.]

Ron, This is awesome. Thank you so much for your help! Max, thanks also for
the attempt. Its very much appreciated.

Justin F.

"Ron Rosenfeld" wrote:

> On Fri, 24 Jun 2005 10:19:13 +0800, "Max" <[email protected]> wrote:
>
> >Superb one, Ron !
> >
> >My flawed attempt was due to an oversimplified interp <g>
> >on the spec's part of preserving the data if there's an "&" inside ..
>
> I tried yours first, and when it didn't return what I thought the OP wanted, I
> decided to work on it.
>
> But I've posted many non-solutions due to misinterpretations of the
> specifications, also <sigh>
>
>
> --ron
>

[Ron Rosenfeld]

On Fri, 24 Jun 2005 10:25:05 -0700, Justin F.
<[email protected]> wrote:

>Ron, This is awesome. Thank you so much for your help! Max, thanks also for
>the attempt. Its very much appreciated.

You're welcome. Thanks for the feedback. Post back if you run into problems
with it.


--ron

[Max]

You're welcome, Justin !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Justin F." <[email protected]> wrote in message
news:[email protected]...
> Ron, This is awesome. Thank you so much for your help!
> Max, thanks also for the attempt. Its very much appreciated.
>
> Justin F.

[JFALK]

I need a similar effect to the one above, but my list contains names in the form LASTNAME, FIRSTNAME MI. and I want to remove the middle initials. Not all of them have a middle initial in them. Thanks!

[Ron Rosenfeld]

On Tue, 28 Jun 2005 15:25:51 -0500, JFALK
<[email protected]> wrote:

>
>I need a similar effect to the one above, but my list contains names in
>the form LASTNAME, FIRSTNAME MI. and I want to remove the middle
>initials. Not all of them have a middle initial in them. Thanks!


Try this:

=IF((LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))<2,
TRIM(A1),LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(
TRIM(A1)," ",CHAR(1),2))-1))


--ron

[JFALK]

On Tue, 28 Jun 2005 15:25:51 -0500, JFALK
<[email protected]> wrote:

>
>I need a similar effect to the one above, but my list contains names in
>the form LASTNAME, FIRSTNAME MI. and I want to remove the middle
>initials. Not all of them have a middle initial in them. Thanks!


Try this:

=IF((LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ","")))<2,
TRIM(A1),LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(
TRIM(A1)," ",CHAR(1),2))-1))


--ron

THANK YOU! It worked perfectly!

[Ron Rosenfeld]

On Wed, 29 Jun 2005 08:57:46 -0500, JFALK
<[email protected]> wrote:

>

>
>THANK YOU! It worked perfectly!

You're welcome. Glad to help.


--ron

[dave glynn]

I have a list of names that includes at leats one first name.

I need to reduce the first name to its initial so that my list which is


William Smith

Joseph Bloggs
etc


becomes

W Smith

J Bloggs

etc...


Thanks


Dave Glynn

[Dave Peterson]

Maybe you could use a formula:

=LEFT(A1,1)&MID(A1,SEARCH(" ",A1),255)



dave glynn wrote:
>
> I have a list of names that includes at leats one first name.
>
> I need to reduce the first name to its initial so that my list which is
>
> William Smith
>
> Joseph Bloggs
> etc
>
> becomes
>
> W Smith
>
> J Bloggs
>
> etc...
>
> Thanks
>
> Dave Glynn

--

Dave Peterson