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:

enter image description here

and here is what I need:

enter image description here

You can access this minimal example here.

2 Answers 2


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.


Solution given by a friend:


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.