Splitting Cells

[jo.stanley]

Hi

Is there a way of splitting one cell and its contents into a number of cells.

For Example

A1 = Mr John Smith

split this to

A1 = Mr
A2 = John
A3 = Smith

Thanks
Jo

 

[Richard Schollar]

Hi Jo

Try selecting the column and going Data>TextToColumns>Delimited option>Next>Space delimter>Finish.

 

[PATSYS]

Highlight the entire column A, click on Data menu, Text to columns, check Delimited, next, choose Space as delimiters, and Finish.

 

[Lewiy]

Go to Data>TextToColumns and select "space" as the delimiter (make sure you have at least 2 blank columns to the right of your data)

 

[Lewiy]

Or if you want to use formulas (this will only work if there's 3 words):

=LEFT(A1,FIND(" ",A1)-1)

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))

 

[ladbroke]

I bumped into this one through search, what if there was 5 words, what would you use then?

 

[Richard Schollar]

Ladbroke

Assuming you don't want to use TextToColumns (which would function perfectly fine) then a possible formula approach might be:

=TRIM(MID(SUBSTITUTE(" "&TRIM($A1)," ",REPT(" ",100)),COLUMNS($A:A)*100+(SUMPRODUCT(LEN($A1:A1))-LEN($A1)),100))

in B1 and copied across.