Split string into separate columns without delimiter

Hi,

in my sheet I have the following certain columns which content i want to split into separate columns.

Column F (9 positions long) --> value on position 1 goes into once column, value on position 2 goes into one column, value on position 3 goes into one column etc..
Column G (24 positions longs) --> value on position 1 goes into once column, value on position 2 goes into one column, value on position 3 goes into one column etc..
Column H (90 positions long) --> value on position 1 goes into once column, value on position 2 goes into one column, value on position 3 goes into one column etc..

How can I do that my using a formula.. I have tried the MID formula but does not get it work.

I have attached the file and would be very grateful if one one can help out

Br,
MMExample split string to columns.xlsx

Gleen, not sure what you are talking about?

Use this formula in cell I2:

=MID($F2,COLUMNS($I:I),1)

You may need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings. Also, you should format the cell as General, rather than Text. Then you can copy this across to Q2 (i.e. the green cells), and then copy those down as required.

Then you can use this formula in cell R2:

=MID($G2,COLUMNS($R:R),1)

Same comments as above, then copy across and down the pink cells. Finally, use this in cell AP2:

=MID($H2,COLUMNS($AP:AP),1)

for the yellow cells.

Hope this helps.

Pete

it can be done with Power Query

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Cmp", Int64.Type}, {"Div", type text}, {"Role      ", type text}, {"Function  ", type text}, {"Sts", Int64.Type}, {"Option 1-9", type text}, {"Function keys", type text}, {"Option 10-99", type text}}),
    Split90 = Table.SplitColumn(Type, "Option 10-99", Splitter.SplitTextByRepeatedLengths(1), {"Option 10-99.1", "Option 10-99.2", "Option 10-99.3", "Option 10-99.4", "Option 10-99.5", "Option 10-99.6", "Option 10-99.7", "Option 10-99.8", "Option 10-99.9", "Option 10-99.10", "Option 10-99.11", "Option 10-99.12", "Option 10-99.13", "Option 10-99.14", "Option 10-99.15", "Option 10-99.16", "Option 10-99.17", "Option 10-99.18", "Option 10-99.19", "Option 10-99.20", "Option 10-99.21", "Option 10-99.22", "Option 10-99.23", "Option 10-99.24", "Option 10-99.25", "Option 10-99.26", "Option 10-99.27", "Option 10-99.28", "Option 10-99.29", "Option 10-99.30", "Option 10-99.31", "Option 10-99.32", "Option 10-99.33", "Option 10-99.34", "Option 10-99.35", "Option 10-99.36", "Option 10-99.37", "Option 10-99.38", "Option 10-99.39", "Option 10-99.40", "Option 10-99.41", "Option 10-99.42", "Option 10-99.43", "Option 10-99.44", "Option 10-99.45", "Option 10-99.46", "Option 10-99.47", "Option 10-99.48", "Option 10-99.49", "Option 10-99.50", "Option 10-99.51", "Option 10-99.52", "Option 10-99.53", "Option 10-99.54", "Option 10-99.55", "Option 10-99.56", "Option 10-99.57", "Option 10-99.58", "Option 10-99.59", "Option 10-99.60", "Option 10-99.61", "Option 10-99.62", "Option 10-99.63", "Option 10-99.64", "Option 10-99.65", "Option 10-99.66", "Option 10-99.67", "Option 10-99.68", "Option 10-99.69", "Option 10-99.70", "Option 10-99.71", "Option 10-99.72", "Option 10-99.73", "Option 10-99.74", "Option 10-99.75", "Option 10-99.76", "Option 10-99.77", "Option 10-99.78", "Option 10-99.79", "Option 10-99.80", "Option 10-99.81", "Option 10-99.82", "Option 10-99.83", "Option 10-99.84", "Option 10-99.85", "Option 10-99.86", "Option 10-99.87", "Option 10-99.88", "Option 10-99.89", "Option 10-99.90"}),
    Split24 = Table.SplitColumn(Split90, "Function keys", Splitter.SplitTextByRepeatedLengths(1), {"Function keys.1", "Function keys.2", "Function keys.3", "Function keys.4", "Function keys.5", "Function keys.6", "Function keys.7", "Function keys.8", "Function keys.9", "Function keys.10", "Function keys.11", "Function keys.12", "Function keys.13", "Function keys.14", "Function keys.15", "Function keys.16", "Function keys.17", "Function keys.18", "Function keys.19", "Function keys.20", "Function keys.21", "Function keys.22", "Function keys.23", "Function keys.24"}),
    Split9 = Table.SplitColumn(Split24, "Option 1-9", Splitter.SplitTextByRepeatedLengths(1), {"Option 1-9.1", "Option 1-9.2", "Option 1-9.3", "Option 1-9.4", "Option 1-9.5", "Option 1-9.6", "Option 1-9.7", "Option 1-9.8", "Option 1-9.9"})
in
    Split9