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?
CodePudding user response：
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 