A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Remove middle initial from "first name middle initial"



 
 
Thread Tools Display Modes
  #1  
Old June 24th, 2005, 02:02 AM
Justin F.
external usenet poster
 
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.

Ads
  #2  
Old June 24th, 2005, 02:20 AM
Max
external usenet poster
 
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 atyahoodotcom
----
"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  
Old June 24th, 2005, 02:39 AM
Max
external usenet poster
 
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 atyahoodotcom
----


  #4  
Old June 24th, 2005, 03:03 AM
Ron Rosenfeld
external usenet poster
 
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  
Old June 24th, 2005, 03:19 AM
Max
external usenet poster
 
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 atyahoodotcom
----


  #6  
Old June 24th, 2005, 03:21 AM
Max
external usenet poster
 
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 atyahoodotcom
----


  #7  
Old June 24th, 2005, 04:08 AM
Ron Rosenfeld
external usenet poster
 
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  
Old June 24th, 2005, 06:25 PM
Justin F.
external usenet poster
 
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  
Old June 24th, 2005, 10:07 PM
Ron Rosenfeld
external usenet poster
 
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  
Old June 25th, 2005, 12:27 AM
Max
external usenet poster
 
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 atyahoodotcom
----
"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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
creating/defining relationship between two tables Giz New Users 2 April 18th, 2005 09:24 PM
how do I remove blank pages in the middle of my word document Tena General Discussions 1 January 20th, 2005 07:07 PM
office 2000 remove tools bud Setup, Installing & Configuration 1 May 27th, 2004 10:40 AM
Identifying Upper case or identifying a single initial Peo Sjoblom Worksheet Functions 1 January 23rd, 2004 07:41 PM
Remove spaces in the middle of a cell? Jacqueline Bendana Worksheet Functions 1 October 1st, 2003 10:07 PM


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


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 OfficeFrustration.
The comments are property of their posters.