Text to Columns help

[Martin ©¿©¬]

Hi
I have a column with name like below

McAllister Mr & Mrs E.
Hay Mrs E.
Farquhar Rev. G. & Mr M.
Russell Mrs M.
Cuthbert Mr & Mrs J.
Cuthbert Mr R.
Cuthbert Miss Judith
Weller Mr & Mrs M.
Doherty Mrs C.
Stewart Mr & Mrs J.
Ridges Mr W.

Is it possibe to do Text to 2 Columns on them all in one go?
I'm having to do long names like McAllister Mr & Mrs E. into 2 colums
McAllister & Mr & Mrs E. & short names Hay Mrs E. into Hay & Mrs E.
seperately which is going to take ages as there are 14 similar pages

Can anyone offer a solution please

 

[Jacob Skaria]

With names in Col A try the formulas in B and C
B1
=TRIM(LEFT(A1,FIND(" ",A1)))
C1
=TRIM(RIGHT(A1,FIND(" ",A1)))

OR

Text to Columns with Space delimiter so that the first column will have names
and combine the remaining 3 or 4 columns using Concatenate() function

If this post helps click Yes

 

[Martin ©¿©¬]

With names in Col A try the formulas in B and C
B1
=TRIM(LEFT(A1,FIND(" ",A1)))
C1
=TRIM(RIGHT(A1,FIND(" ",A1)))
OR
If this post helps click Yes
---------------

Hi Jacob
This is my first time trying this, Can u explain in more detail please

 

[Jacob Skaria]

1. Using formulas

With the name McAllister Mr & Mrs E. in A1 try the below formulas in B1 and
C1. If that does work copy the formula down to the number of rows where you
have the names in A1.

2. Text to Columns

Select the data range. From menu Data>TExt to Columns> will display the
first few samples in teh data preview area.>Next> will take you to step 2. By
default tab will be ticked. Take off that and check only Space. and Next>
This will put a line breaker in between the spaces. Next>Finish. this will
split the data between spaces. Maintain Column A as such For B,C,D and E
might have information which needs to be combined. Use a formula in Col F
such as
In cell F1
=B1&" "&C1&" "&D1&" "&E1
will combine the initials and titles with a space delimiter...Now copy the
formula to all rows.. Once done copy> paste special> values to the next
column or to the same column........

 

[Martin ©¿©¬]

Thanks Jacob
For some reason that wouldn't work for me