Remove middle initial from "first name middle initial"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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))
 
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"]
 
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.
Click to expand...

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
 
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 ..
 
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 ..
Click to expand...

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, This is awesome. Thank you so much for your help! Max, thanks also for
the attempt. Its very much appreciated.

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

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


--ron
 
I need a similar effect to the one above, but my list contains names i
the form LASTNAME, FIRSTNAME MI. and I want to remove the middl
initials. Not all of them have a middle initial in them. Thanks
 
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!
Click to expand...


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
 
Ron said:
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
Click to expand...


THANK YOU! It worked perfectly!
 
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
 
Back
Top