Split up text part of cell into different columns

[jk51]

Hi,
I got a list of 1000 ww1 soldiers records.
How to split up the text in cell A1 below into next 4 columns - (B1) Name, (C1) Corps Regiment, (D1) No and (E1) Rank?

for example in cell A2 and A3 -

Amar, Bedi. Corps Regiment No Rank 36th Sikhs 3687 Sepoy. Atma, Singh. Corps Regiment No Rank 15th Sikhs 395 Naik.

in cell B2 output = Amar, Bedi.
in cell C2 output = 36th Sikhs
in cell D2 output = 3687
in cell E2 output = Sepoy.

in cell B3 output = Atma, Singh.
in cell C3 output = 15th Sikhs
in cell D3 output = 395
in cell E3 output = Naik.

etc..

Thanks,
Mr Singh

 

[GraH - Guido]

Try text to column, or Power Query, aka Get & Transform (on the ribbon) then split column.
Looking at the data, and the lack of a clear delimiter, I suspect if the data is the result of an export, the length of the fields might be the same. There is a reason we ask to upload sample file.

 

[bosco_yip]

Try...................

In B2, formula copied across and down :

=TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",50)),INDEX(50*{1,7,9,10},COLUMN(A1)),INDEX({99,99,50,50},COLUMN(A1))))



Regards

 

[albanvanrijsewijk]

Try...................

In B2, formula copied across and down :

=TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",50)),INDEX(50*{1,7,9,10},COLUMN(A1)),INDEX({99,99,50,50},COLUMN(A1))))

View attachment 65628

Regards

Hi,

Can you explain that formula for me please? I'm impressed by the result and can't manage to deconstruct the formula

Thanks

 

[jk51]

Try...................

In B2, formula copied across and down :

=TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",50)),INDEX(50*{1,7,9,10},COLUMN(A1)),INDEX({99,99,50,50},COLUMN(A1))))

View attachment 65628

Regards

Thanks very much indeed. It's works!! Bw Mr Singh

 

[AlanSidman]

An alternative solution is to use Power Query. Here is the Mcode and a file attached to show you the steps.

l

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2", "Column1.2.3", "Column1.2.4", "Column1.2.5", "Column1.2.6", "Column1.2.7", "Column1.2.8", "Column1.2.9"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}, {"Column1.2.3", type text}, {"Column1.2.4", type text}, {"Column1.2.5", type text}, {"Column1.2.6", type text}, {"Column1.2.7", type text}, {"Column1.2.8", Int64.Type}, {"Column1.2.9", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Column1.2.6", "Column1.2.7"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1.2.1", "Column1.2.2", "Column1.2.3", "Column1.2.4", "Column1.2.5", "Column1.3"})
in
    #"Removed Columns"