Excel 2016 : Issue with CHAR(10) i.e New line-CodePudding

I have a data in excel, example shown below

Column1 (Cell A1)
A

B

P.S. - The value shown above are in 1 single cell and not 3 rows. (So, Char(10) is like a blank line cell value looks like below)

I need to remove the new line characters (Blanks) in between that cell value.

Output example,

Column1
A       
B

There are number of cells like this in my column.

My efforts:

I tried to do this with replace/substitute the Char(10) character but that removes all Char(10) and I get wrong output,

SUBSTITUTE(Column1,CHAR(10),"")

Wrong output:

Column1
AB

Could anyone please suggest a solution to achieve this? I can try something else as well to resolve this issue.

CodePudding user response：

In A1, enter A and B with multiple line break in between

In B1, enter formula with resulted in only left one line break in between :

=SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(10)," "))," ",CHAR(10))

Remark : To format the cell with Wrap Text

CodePudding user response：

I would think:

=SUBSTITUTE(A1,CHAR(10)&CHAR(10),CHAR(10))

and as @Rory recommended - turn on Wrap Text on the cell from the Alignment menu.