Split multi-line cell into columns using data prefix as column heading

Question

I have cells with multiple lines e.g.

Column
Name: J Smith Address: 123 Main St Phone: 14380

Now, using the "Text to Columns" button I can split the rows into separate columns. So far so good.

However, my problem is that some cells don't have data on every row. For example:

Column
Name: J Smith Address: 123 Main St Phone: 14380
Address: 3 Green St Phone: 54778

When I split the above, I get misaligned columns like so:

Column	Column	Column
Name: J Smith	Address: 123 Main St	Phone: 14380
Address: 3 Green St	Phone: 54778

Is there a way to make the columns aligned, so that all the 'name' values are in the same column, all address values in the same column etc?

Answer 1

You can try FILTERXML() with starts-with or contains argument. try-

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(10),"</s><s>")&"</s></t>","//s[starts-with(., 'Name')]"),"")
=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(10),"</s><s>")&"</s></t>","//s[starts-with(., 'Address')]"),"")
=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,CHAR(10),"</s><s>")&"</s></t>","//s[starts-with(., 'Phone')]"),"")

For details about FILTERXML() read this article from JvdV.