MULTIPLE COLUMNS DATA IN ONE ROW WITH DIFFERENT LINES

[jams]

Dear All,

I need your cool response to my below request.

I have an excel file consist if 10,545 coloumns, data transfered from AS400. File is uploaded for your reference.

if you see this file coloumn B is one line data but this is splited to many lines and same thing repeating in C and D, this C & D line should be tranfered to B with seperate line.

Column B row # 3 will be in three lines if you shift data from C&D
Column B row # 4 will be in four lines if you shift data from C,D & E.

numbers in Coloumn G & H should be in "E" & "F" coloumn.
this data is repated many times in this file. please need your quick response to solve this issue.

regards
Jams

 

[lohithsriram]

Hi Jams,

Use a helper column (insert a column between A and B) and add the below formula on B3 and B4 Cells.
=SUBSTITUTE(C3,CHAR(10),"") &CHAR(10) &SUBSTITUTE(D3,CHAR(10),"") &CHAR(10) &SUBSTITUTE(E3,CHAR(10),"")

=SUBSTITUTE(C4,CHAR(10),"") &CHAR(10) &SUBSTITUTE(D4,CHAR(10),"") &CHAR(10) &SUBSTITUTE(E4,CHAR(10),"")&CHAR(10) &SUBSTITUTE(E4,CHAR(10),"")

For E4 and F4 column, simply refer to =G4 and =H4.

Sorry that I cannot upload the file from my PC here.

Hope this works for you.

 

[jams]

thank u very much Dear Lohith,
hope it will work, apprciate u and god bless you always.

regards
Jams