Converting Multiline Cell values to Single cell values

[M_S_K]

Hi All,

I would like to know how to handle the multiline cell values in Excel.

I have a source excel file in the below format.

Key	Country/State
A0001	Australia Sydney Perth
A0002	India Tamilnadu Andhra

I need the output to be in below format.

Key	Country	State
A0001	Australia	Sydney
A0001	Australia	Perth
A0002	India	Tamilnadu
A0002	India	Andhra

Most of the online articles suggest to use "Text to Columns" with "\n" character as the delimited to break the multi-line cell to single cell. Seems this option won't work for me as the data will be pasted in column wise after the "Text to Columns" operation. Is there any other better way to get the desired output? Your responses are highly appreciated.

Thank You.

 

[Marc L]

Hi,​

​

as you forgot to indicate where should be the 'output'​

 

[Marc L]

​

Another question : is the value 'A0001' for example is some merged cells like when copying your initial 'attachment' to a worksheet ?​

​

If not - within a single cell - so it's better to link your sample workbook on a files host website …​