# Split a column and rearrange it on Google Sheets

I have a column with three codes separated by space. These codes are M, N or T followed by a one digit number (e.g M1 N3 T2, or N2 T1, or just M1), always in the same order: first M, then N, then T, and some of them could be missed. However, all the cells have data.

I want to split that column in three columns, which I know how to do with

``````=ARRAYFORMULA(split(A:A," ",false,true))
``````

for example, but I also want the M to be in the first column, the T in the second and the N in the third (notice the permutation from MNT to MTN).

Here is the example of what I arrived to do:

and here is what I need:

You can access this minimal example here.

I've added a new sheet ("Erik Help") with the following formula in B1:

`=ArrayFormula({"Codes","",""; IF(A2:A="",,IFERROR({REGEXEXTRACT(A2:A,"M."),REGEXEXTRACT(A2:A,"T."),REGEXEXTRACT(A2:A,"N.")}))})`

This one formula will produce all results for the three columns.

• Amazing. Thanks! Jun 19, 2021 at 18:13
• You're welcome. Jun 19, 2021 at 19:59

Solution given by a friend:

``````=ARRAYFORMULA(MID(A:A,FINDB("M",A:A),2))
``````

in the first column. The same with "T" in the second and similar with "N" in the third.