CODEWITHSUNDEEP
arraysexcelvbasplit
Andy M
Andy M

Reputation: 187

SPLIT Function: Text from a cell into an array but ignore the blank lines

Is there a way to populate an array using the split function but ignoring the blank lines.

I have a workbook that has a transcripts in it. There is 1 cell per transcript and the cell looks something like this:

01/04/2019 09:05:18 - Test User 2 (Additional Comments)
Hello


01/04/2019 09:04:43 - Test User 1 (Additional Comments)
Hello

Currently I am using this code:

txt = ActiveCell.Value

FullName = Split(txt, vbLf)

Which splits the cell text up nicely but it is including all the blank lines as well. is there a way to use the split fucntion and then don't populate the array with the blank lines?

cheers

EDIT:

Now using

txt = ActiveCell.Value
FullName = RemoveBlankLines(Split(txt, vbLf))
For i = UBound(FullName) To 0 Step -1
    Debug.Print FullName(i)
Next i

Function RemoveBlankLines(Strings As Variant) As Variant
    Dim v As Variant
    Dim i As Long, j As Long

    ReDim v(LBound(Strings) To UBound(Strings))
    j = LBound(Strings) - 1
    For i = LBound(Strings) To UBound(Strings)
        If Trim(Strings(i)) <> "" Then
            j = j + 1
            v(j) = Strings(i)
        End If
    Next i

    If j >= LBound(Strings) Then
        ReDim Preserve v(LBound(Strings) To j)
        RemoveBlankLines = v
    End If
End Function

Thank you

Upvotes: 2

Views: 2648

Answers (4)

EvR
EvR

Reputation: 3498

Another one, just for the fun:

txt = Split(Replace(Join(Filter(Split("~" & Replace(ActiveCell.Value, vbLf, "~|~") & "~", "|"), "~~", False), vbLf), "~", ""), vbLf)

Upvotes: 1

Jitendra Singh
Jitendra Singh

Reputation: 191

In shorthand to one of the other answers, you can also do it like this:

Split(Replace(txt, vbLf + vbLf, ""), vbLf)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152575

You can always replace the two back to back line feeds with one:

txt = Replace(txt, vbLf, "|")
txt = Replace(txt, "||", "|")
txt = Replace(txt, "||", "|")
txt = Replace(txt, "||", "|")

FullName = Split(txt, "|")

Upvotes: 2

John Coleman
John Coleman

Reputation: 52008

You could fix the result of the split:

Function RemoveBlankLines(Strings As Variant) As Variant
    Dim v As Variant
    Dim i As Long, j As Long

    ReDim v(LBound(Strings) To UBound(Strings))
    j = LBound(Strings) - 1
    For i = LBound(Strings) To UBound(Strings)
        If Trim(Strings(i)) <> "" Then
            j = j + 1
            v(j) = Strings(i)
        End If
    Next i
    If j >= LBound(Strings) Then
        ReDim Preserve v(LBound(Strings) To j)
        RemoveBlankLines = v
    End If
End Function

Then use 

FullName = RemoveBlankLines(Split(txt, vbLf))

Upvotes: 3

Related Questions