Split and Transpose a group of words into multiple cells in Google Sheets

Question

I would like to ask if it is possible to split and transpose a group of words from one cell to multiple cells in a spreadsheet. This is what it looks like in cell A1:

Zhen Rong Yu Merrill Lynch, Pierce, Fenner & Smith Incorporated

9595 WILSHIRE BLVD

BEVERLY HILLS, CA 90212

I would like to know if its possible to look like this:

A1: Zhen Rong Yu 
B1: Merrill Lynch, Pierce, Fenner & Smith Incorporated
C1: 9595 WILSHIRE BLVD
D1: BEVERLY HILLS, CA 90212

Answer 1

This may get you started:

=transpose(split(substitute(A1,Char(10),"%"),"%"))

But:

1. The results would not start in A1 (which can't hold a value and a formula at the same time). Maybe enter it in B1, select ColumnB, Copy, Paste Special, Paste values only and then delete ColumnA.

2. There is no way to automate splitting Zhen Rong Yu from Pierce, Fenner & Smith Incorporated unless there is some consistency between the first or second parts with examples not shown, or there is a delimiter (added manually?) between the two parts, or you have a lookup table to show which part is which.

3. You may end up with needing to filter (or equivalent) to delete blank rows, depending upon what is causing the blank rows (if any) in A1.

TRANSPOSE
SPLIT
SUBSTITUTE