Separating Names in a Cell

I have another trade list where the names are listed Bill & Mary Williams. I
know I can use the data function, text to columns to separate Bill & Mary
using the delimited function using other (&). Is there any way of separating
between Mary and Williams into separate cells?????
--
Motaad

I am assuming you want to split "Bill & Mary Williams" to "Bill & Mary"
"Williams". Use find and replace function to replace all " & " with "-&-"
and then use Data-Text to Columns to split using spaces as the delimiter.
Then use find and replace to replace all "-&-" with " &".

If you want to split "Bill & Mary Williams" to "Bill" "Mary" "Williams" then
use data text to columns and select both spaces and other "&". And select
"treat consectutive delimiters as one"

"Motaad" wrote:

> I have another trade list where the names are listed Bill & Mary Williams. I
> know I can use the data function, text to columns to separate Bill & Mary
> using the delimited function using other (&). Is there any way of separating
> between Mary and Williams into separate cells?????
> --
> Motaad

Use Text to columns with a space and a & separator

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Motaad" <Motaad@discussions.microsoft.com> wrote in message
news:798478AA-EC4F-4165-8664-2707E81F71D8@microsoft.com...
> I have another trade list where the names are listed Bill & Mary Williams.
I
> know I can use the data function, text to columns to separate Bill & Mary
> using the delimited function using other (&). Is there any way of
separating
> between Mary and Williams into separate cells?????
> --
> Motaad

if your data is in A1 put this function in B1

=MID(A1,FIND(" ",A1,FIND("&",A1)+2)+1,100)

Originally Posted by Motaad

I have another trade list where the names are listed Bill & Mary Williams. I
know I can use the data function, text to columns to separate Bill & Mary
using the delimited function using other (&). Is there any way of separating
between Mary and Williams into separate cells?????
--
Motaad