How to delete blank lines in a cell?

[Arjunspaudel]

Hi, I need to delete blank lines in a cell. Example :
124343423
blank line
314342366
132131241
blank line

I want to get the result as:
124343423
314342366
132131241

Any help would be very much appreciated. Thank you very much in advance.

 

[mole999]

just to clarify, you have multiple lines in multiple cells ?

 

[Arjunspaudel]

just to clarify, you have multiple lines in multiple cells ?

Yes I do have multiple cells with same problems, but the above example is content of one cell only.

 

[mikerickson]

Have you looked at =SUBSTITUTE(A1, CHAR(13)&CHAR(13), CHAR(13))

 

[Arjunspaudel]

Have you looked at =SUBSTITUTE(A1, CHAR(13)&CHAR(13), CHAR(13))

It didn't work when I have content only in first line and 2nd and 3rd line are blank.

 

[navic]

Perhaps this formula and 'Wrap Text'

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

 

[navic]

I apologize,
@mikerickson formula works if enable Wrap Text

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

 

[Arjunspaudel]

Perhaps this formula and 'Wrap Text'

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

Thank you for your help. This formula will bring everything in first line.

 

[Arjunspaudel]

I apologize,
@mikerickson formula works if enable Wrap Text

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

This formula wont work if first or the last lines are blank.

 

[Arjunspaudel]

Here's the solution I found in other forum. It works!!

How can I remove blank line breaks from an excel cell with VB or a formula?

I have many cells where data is broken up into many lines; some of those lines are blank. Is there a VB function or formula to remove blank line breaks in Excel cells, or all line breaks?

stackoverflow.com