Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Justin F.
 
Posts: n/a
Default Remove middle initial from "first name middle initial"

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.

  #2   Report Post  
Max
 
Posts: n/a
Default

One way to try ..

Assuming the data is n A2 down,

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Justin F." <Justin wrote in message
...
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.



  #3   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----


  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 23 Jun 2005 18:02:02 -0700, Justin F. <Justin
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
  #5   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----




  #6   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----


  #7   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Fri, 24 Jun 2005 10:19:13 +0800, "Max" 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
  #8   Report Post  
Justin F.
 
Posts: n/a
Default

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

  #9   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Fri, 24 Jun 2005 10:25:05 -0700, Justin F.
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
  #10   Report Post  
Max
 
Posts: n/a
Default

You're welcome, Justin !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Justin F." wrote in message
...
Ron, This is awesome. Thank you so much for your help!
Max, thanks also for the attempt. Its very much appreciated.

Justin F.





  #11   Report Post  
JFALK
 
Posts: n/a
Default


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!


--
JFALK
------------------------------------------------------------------------
JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728
View this thread: http://www.excelforum.com/showthread...hreadid=381827

  #12   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Tue, 28 Jun 2005 15:25:51 -0500, JFALK
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
  #13   Report Post  
JFALK
 
Posts: n/a
Default


Ron Rosenfeld Wrote:
On Tue, 28 Jun 2005 15:25:51 -0500, JFALK
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!


--
JFALK
------------------------------------------------------------------------
JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728
View this thread: http://www.excelforum.com/showthread...hreadid=381827

  #14   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 29 Jun 2005 08:57:46 -0500, JFALK
wrote:




THANK YOU! It worked perfectly!


You're welcome. Glad to help.


--ron
  #15   Report Post  
dave glynn
 
Posts: n/a
Default

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


  #16   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"