Delete new line if number of characters is less than 20

ymcata · Dec 20th 2018

Hi,

i have multiple rows in one column, where each cell contains multiple lines (char 10). I would like to keep just lines, where number of characters in each line is bigger than 20.
The result in B2 would have 2 lines (sentences) in B3 would have 3 lines (sentences). [TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td]

A

[/td]

[td]

B

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

[TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td]

short
aerewre
This is the sentence, I would like to keep
hg 34f 43f4
sdifsd 23423
Also sentence I would like to keep

[/td]

[/tr]

[/TABLE]

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

shorwerww 23233
aerewre 333
This is the sentence, I would like to keep and save
hg 34f 43f4
sdifsd 23423
Also sentence I would like to keep
Also sentence I would like to keep

[/td]

[/tr]

[/TABLE]

Could you please help me with VBA or either formula in Column C.

Thank you in advance and best regards,
Marko

Marius44 · Dec 20th 2018

Hi
Excuse my english (i'm italian)
With VBA code like this (results on column C)

Code

Sub Try()
ur = Cells(Rows.Count, 2).End(xlUp).Row
a = ur
For i = 2 To ur
  cel = Cells(i, 2).Value
  spr = Split(cel, Chr(10))
  For j = 1 To UBound(spr)
    If Len(spr(j)) >= 20 Then
      a = a + 1
      Cells(a, 3) = spr(j)
    End If
  Next j
Next i
End Sub

Display More

I hope helped you. Bye, bye
Mario

Marius44 · Dec 20th 2018

Hi
I beg your pardon. I did not understand correctily.
This is the correct code

Code

Sub TryAgain()
ur = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To ur
  cel = Cells(i, 2).Value
  spr = Split(cel, Chr(10))
  For j = 0 To UBound(spr)
    If Len(spr(j)) >= 20 Then
      txt = txt & spr(j) & Chr(10)
    End If
  Next j
  Cells(i, 3) = txt
  txt = ""
Next i
End Sub

Display More

Bye,
Mario

ymcata · Dec 21st 2018

Hi Mario,

first i would like to thank you for your time.

The code is not working - it breaks with Split function and returns me this error:
"Expected variable or procedure, not module"

Could you please check what's the issue?

Thank you in advance and best regards,
Marko

Marius44 · Dec 21st 2018

Hi Marko
I don't know.
I upload my file that works correctly.
I have added Option Explicit and variables declaretions.

Ciao,
Mario

ymcata · Dec 21st 2018

Hi Mario,

my bad - i had module in my workbook with name "Split" and also haven't had variables accurate. Your workbook helped me a lot and saved plenty of time.

Thank you again and all the best,
Marko

Marius44 · Dec 21st 2018

Hi Marko
I'm glad to have been helpful.
Thank you for your courteous deferment.
Best wishes for the next holidays.

Bye,
Mario

Delete new line if number of characters is less than 20

Participate now!

Share