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