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)".
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)