Separate data within cell to different columns.

[craigwojo]

I'm looking to take data in each cell within a column of cells and have the following columns (N,O,P,Q,R,S) take the data separated by (a space) and put it in the columns.

How can I do this?

Here is an example I made to show what I am looking for.

Thank you all so much and God bless.

Leprino Foods Tags Order Motor Numbers.xls
	M	N	O	P	Q
1	Lock number	Place 1	Place 2	Place 3	Place 4
2	E1683 CV113-MT05 CV118-MT01	E1683	CV113-MT05	CV118-MT01
3	E1684 CV113-MT06 CV119-MT01	E1684	CV113-MT06	CV119-MT01
4	E1685 CV94-MT01	E1685	CV94-MT01
5	E1686 CV120-MT01	E1686	CV120-MT01
Sheet1

 

[hagia_sofia]

Hello, one option could be:

=IFNA(DROP(REDUCE("",M2:M5,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),1),"")

 

[dreid1011]

Nevermind, I suppose this does not place the data starting in col N, but in M.

On the Data tab of the ribbon, use the Text to Columns tool and set the delimiter to space.

 

[Scott Huish]

=TEXTSPLIT(M2," ")

Copy down.

Book1
	M	N	O	P	Q
1	Lock number	Place 1	Place 2	Place 3	Place 4
2	E1683 CV113-MT05 CV118-MT01	E1683	CV113-MT05	CV118-MT01
3	E1684 CV113-MT06 CV119-MT01	E1684	CV113-MT06	CV119-MT01
4	E1685 CV94-MT01	E1685	CV94-MT01
5	E1686 CV120-MT01	E1686	CV120-MT01
Sheet1

Cell Formulas
Range	Formula
N4:O5,N2:P3	N2	=TEXTSPLIT(M2," ")
Dynamic array formulas.

 

[alansidman]

Use Power Query and Close and Load to appropriate column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Lock number", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Lock number.1", "Lock number.2", "Lock number.3"})
in
    #"Split Column by Delimiter"

 

[Peter_SSs]

I suppose this does not place the data starting in col N, but in M.

It can start in N.

1. Select M2:M(last row)
2. Data ribbon tab -> Text to Columns -> Delimited -> Next -> Select Space only -> Next -> Enter N2 as the Destination -> Finish

 

[craigwojo]

Thank you everyone. God bless.
Craig

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.