Search code examples
vbaexcel-2007

How to remove empty lines within multiple lines that reside in one cell?


I got results from Outlook into Excel using VBA. There are some cells that hold multiple lines in one cell, like this

line 1
line 2



another line


final line

I tried regular expression "^$" to remove the empty lines.

And worse, since all the lines are in once cell, I cannot use a formula to merge them.

What is in between the lines? I used "^$" to match them and replace with "". That means there is nothing left but still the empty lines are there. I also removed "chr(13)".


Solution

  • Do you want to remove all line breaks?

    YourResult = replace(replace(YourCellValue, chr(10), ""), chr(13), "")
    

    Or do you just want to remove the empty lines?

    [edit] The answer above wasn't tested. I see now that the character separating the lines is chr(10), so you only need to replace that:

    YourResult = replace(YourCellValue, chr(10), "")
    

    Also, if you want to keep multiple lines, but remove the empty lines, you can replace two line breaks with one, and repeat that step until no changes are left:

    Function FixLines(value As String)
      Do
        s = value
        value = Replace(value, Chr(10) & Chr(10), Chr(10))
      Loop Until value = s
      FixLines = value
    End Function
    
    YourResult = FixLines(YourCellValue)