Another, sort of trim question

[stapleton2308]

Thanks for you help earlier (TWIMC)

If i have a list as follows:

SMITHMRD&MRSE
JONESMRF
ELLIOTMRM&MRSK
JOHNSMRS&MRSP

As you can see its's a surname 1st, then, ie its Mr S & Mrs K, is there
anyway this can be seperated so it looks like below?:

SMITH MRD&MRSE
JONES MRF
ELLIOT MRM&MRSK
JOHNS MRS&MRSP

Help much appricated!!!

 

[Cutter]

For a start you could use:

=LEFT(A1,SEARCH("MR",A1)-1)&" "&RIGHT(A1,LEN(A1)-SEARCH("MR",A1)+1)

which works for the samples you gave but it wouldn't work for MS or
MISS

 

[Robert_Steel]

If you wish to look for more than one string

=SUMPRODUCT(IF(ISERROR(SEARCH({"miss","mr","ms"},M8)),"",SEARCH({"miss","mr","ms"},M8)))
will return the location of the first occurance.
This could be sandwiched in a
=Replace(M8,Formula,0," ")
to insert a space

=Left(M8,Formula)
for the surname

=Right(M8,Len(M8)-Formula)
or
=Replace(M8,1,Formula-1,"")
for the Title and initials.

It will breakdown if you have more than one of the search strings in the
name.
Also I guess you have spotted that searching for a string is inherently
going to fail if the name contains the search string.
eg. HAMRICKMR&MRSLK
However it may bring the list down to a few manageable errors.

hth RES