Separating Names in a Cell

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

Guest

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?????
 
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"
 
Use Text to columns with a space and a & separator

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
if your data is in A1 put this function in B1

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