How to separate every line??

[Ratan Bhushan]

Hi Team

I have attached the file where I have data in cell A1. In which, there are some special characters which are separating the lines.

What I am looking, to make separate records of every line.

Is there any way either by formula or VBA.

Please help.

Thanks
Ratan

 

[Ramesh]

One solution using formula will be concatenating the lines by using char(10) i.e. if you write the formula as ="A"& char(10) &"B", then A & B will be shown in 2 lines..

 

[deciog]

Ratan Bhushan

Was that what you wish

=SUBSTITUTE(A1;CHAR(10);" ")

Decio

 

[prasaddn]

Hi,

your data file seems to be one data.. what you want to separate in them? every new line in a separate cell?

if yes, use vba.split() function with delimiter as Char(10)

and store the array into rows or columns you want.

Regards,
Prasad DN

 

[shrivallabha]

In cell A2:
=TRIM(MID(SUBSTITUTE(CHAR(10)&$A$1,CHAR(10),REPT(" ",99)),ROWS($A$2:$A2)*99,99))
And then copy down.

Is it what you want?

 

[bosco_yip]

Or......

In cell A2, formula copy down :

=TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE($A$1,CHAR(13),CHAR(10)),CHAR(10)," "))," ",REPT(" ",500)),ROWS($A$2:$A2)*500,500))

Edit : the data line separator found in CHAR(10) and CHAR(13)

Regards
Bosco