Yet another transformation of multiple line cells ...

Hello,
I have an ugly XLS file with multiple line cell content that is ugly & huge, and where I need to do some HLOOKUP adn VLOOKUP on the data stored in column D
But to do that I first need to remove those stupid multiple line cells in order to do that.

The file looks like the following :

A B C D
1 XXX Name 1 Ref 1 Ref a
Ref b
Ref c
2 YYY Name 2 Ref 2 Ref d
Ref e
Ref f
Ref g

I'd like to remove the multiple line cells in column D by adding the other lines next to column D (that is filling columns E and following) ..

A B C D E F G
1 XXX Name 1 Ref 1 Ref a Ref b Ref c
2 YYY Name 2 Ref 2 Ref d Ref e Ref f Ref g

I found a very nice article on this forum that makes the split in lines ... but not in column.
See here : http://www.excelforum.com/excel-form...rate-rows.html

I'm investigating with my limited VBA & XL skills ... so support welcome !

Hello,

For sure, I should have indeed done that instead of trying to make a symbolic xls file in the thread text
Here it is !

Extract XLS file.xlsx

Be my guest
Extract XLS file completed.xlsx

Hello !

Thanks a lot for your reply, I just tried your formula and it works great !
I was not aware of the difference between formula and array formula ... so your instructions were very helpfull ...

I had to replace the commas by semicolons ... but the result is just amazing !

Thanks again !!!!

Merci ! Bonne journee egalement ... How do you know I'm french ?

Indeed ! Betrayed by my profile's information
Have a nice day in Yorkshire then ! :D