Splitting mullti-line cells into separate rows and repeat values in columnA after split

I have an excel doc with a few columns, some of them contain several lines per cell.
I need to convert them into columns where each row contains single line.
I column A is Name which has to be repeated after split.
Sometimes in columnA is only Name and I need to keep this row.

Example
Name1| Tra1 |Date1 |Rat1
| Tra2 |Date2 |Rat1
Name2| | |
Name3|Tra3 |Date3 |Rat3
|Tra4 |Date4 |
|Tra5 |Date5 |Rat5

Name1 is one row with 2 lines in the cells
Name3 is one row with 3 lines in the cells

Output (each row has only 1 line):

Name1|Tra1 |Date1 |Rat1
Name1|Tra2 |Date2 |Rat1
Name2| | |
Name3|Tra3 |Date3 |Rat3
Name3|Tra4 |Date4 |
Name3|Tra5 |Date5 |Rat5
I have attached a file with the data.

Any suggestions ?

Copy code to a standard module.
Run: SplittingMultiLineCellsIntoSeparateRows

Please Login or Register  to view this content.

try

Please Login or Register  to view this content.

ExampleWithCode.xlsm

Hi Jindon,
Thanks for the code. It worked on the spreadsheet I've attached as an example but when I used the real data I got an error 13 'Type mismatch' on line txt = Join$(Application.Transpose(.Columns("b").Value)).
I am attaching the file.
I was wondering why I got error 13.
Thanks again for the help. I really appreciate it.

Ah, Transpose function will not work when any cell has over 255 characters...

How about
ExampleWithCode2.xlsm