Removing Break Lines in Cell

[Dzirt07]

Hello,
I have a problem. I'm scrapping information from internet but when I place this info into cell It has a lot of break lines

[TABLE="width: 358"]
<tbody>[TR]
[TD="class: xl63, width: 358"]" MLS Number   Category  Status  Status Date  Price  
    
 
    
 7068423  RES Temporarily Off Market 11/14/17 $150,000 
 
    
    
    
 6735873  RES Withdrawn 11/01/16 $199,900 
 
    
    
    
 6525795  RES Expired 05/24/15 $209,900 
 
    
    
    
 3038934  RES Expired 12/31/01 $79,900 
 
    
    
    
 1319046  RES Expired 06/30/01 $79,900 "[/TD]
[/TR]
</tbody>[/TABLE]

I need to remove break lines but not all of them

Info should be close to this format

"MLS Number Category Status Status Date Price 7068423 RES Temporarily Off Market 11/14/17 $150,000
6735873 RES Withdrawn 11/01/16 $199,900
6525795 RES Expired 05/24/15 $209,900
3038934 RES Expired 12/31/01 $79,900
1319046 RES Expired 06/30/01 $79,900
"

 

[Rick Rothstein]

For your text in cell A1, run this macro to reduce it to single line feeds between line...

Sub ReduceLineBreaks()
  Dim Txt As String, vNum As Variant
  Txt = Range("A1").Value
  Do While InStr(Txt, " " & vbLf)
    Txt = Replace(Txt, " " & vbLf, vbLf)
  Loop
  Do While InStr(Txt, vbLf & " ")
    Txt = Replace(Txt, vbLf & " ", vbLf)
  Loop
  For Each vNum In Array(121, 13, 5, 3, 3, 2)
    Txt = Replace(Txt, String(vNum, vbLf), " ")
  Next
  Range("A1").Value = Txt
End Sub

 

[Dzirt07]

Thank you Rick! Your code is working perfectly
I get desirable result when I changed

[COLOR=#333333]Txt = Replace(Txt, String(vNum, vbLf), " ")[/COLOR]

to

[COLOR=#333333]Txt = Replace(Txt, String(vNum, vbLf), [/COLOR][COLOR=#333333]vbLf[/COLOR][COLOR=#333333])[/COLOR]

Sub ReduceLineBreaks()  Dim Txt As String, vNum As Variant
    Txt = Range("A1").Value
    Do While InStr(Txt, " " & vbLf)
      Txt = Replace(Txt, " " & vbLf, vbLf)
    Loop
    Do While InStr(Txt, vbLf & " ")
      Txt = Replace(Txt, vbLf & " ", vbLf)
    Loop
    For Each vNum In Array(121, 13, 5, 3, 3, 2)
      Txt = Replace(Txt, String(vNum, vbLf), vbLf) ' changed " " to vblf 
    Next
    Range("A1").Value = Txt
End Sub

However, do you have an idea how to make

[COLOR=#333333]Array(121, 13, 5, 3, 3, 2)[/COLOR]

dynamic ?
I mean this code works perfectly for the first example but if I will have a different data in cell

Example

" MLS Number   Category  Status  Status Date  Price  

 


 5351126  RES Settled 06/25/08 $185,000 
 






 4604804  RES Expired 07/19/06 $189,900 
 






 4568283  RES Withdrawn Relisted 09/20/05 $199,900 
 






 1202349  RES Settled 05/03/00 $86,000 
 
  
  
  
 
 
  
 
  
"

 

[Rick Rothstein]

Thank you Rick! Your code is working perfectly
I get desirable result when I changed

[COLOR=#333333]Txt = Replace(Txt, String(vNum, vbLf), " ")[/COLOR]

to

[COLOR=#333333]Txt = Replace(Txt, String(vNum, vbLf), [/COLOR][COLOR=#333333]vbLf[/COLOR][COLOR=#333333])[/COLOR]

I am not sure where that B came from... it was supposed to have been vbLf as you figured out. Sorry for that (posted it just before going to sleep, so maybe I was over-tired) but I am glad you were able to figure out what I meant to post.

However, do you have an idea how to make

[COLOR=#333333]Array(121, 13, 5, 3, 3, 2)[/COLOR]

dynamic ?
I mean this code works perfectly for the first example but if I will have a different data in cell

That array does not have to change... the loop using it will reduce up to 9840 consecutive Line Feeds down to a single Line Feed. When I use the code (with the change you noted) on your latest example, it seems to work fine for me. Are you saying it does not work correctly for you? If so, in what way (can you post what you get from the macro and what you expected it to be)?

 

[Dzirt07]

Rick,

I upload my file https://drive.google.com/file/d/11g9Vv-_HmxYlt-zVKnVYzpXvAnU9bh_b/view?usp=sharing

macro works only with cell A2 ( or C2) but doesn't work with others

Could you plz help me figure out what am I missing?

This is example of data from cell C4

[TABLE="width: 391"]
<tbody>[TR]
[TD] MLS Number   Category  Status  Status Date  Price 



 



 6949664  COM Expired 09/18/17 $350,000

 







 6949621  COM Expired 09/18/17 $599,000

 







 4331462  COM Settled 10/08/04 $230,000

 







 4295611  COM Withdrawn Relisted 02/27/04 $225,000

 







 1024196  COM Settled 08/31/98 $215,848

 

 

 

 

 

 

 

 

  
[/TD]
[/TR]
</tbody>[/TABLE]