VBA: Delete lines in cells starting with an alphabet

[jiobi]

I am looking for a simple VBA code to Delete lines in cells starting with an alphabet.
In each cell in column c, there is a multiline text starting with numbers, special characters, and words.
I also delete the space before them if any. Some cells are empty.

 

[JEC]

How about this
I assumed that the data is in column C and that column D is empty

Sub jec()
 Dim ar, sp As Variant, sq As Long, i As Long, j As Long
 ar = Range("C1", Range("C" & Rows.Count).End(xlUp)).Resize(, 2)
 
 For i = 1 To UBound(ar)
     sp = Split(ar(i, 1), vbLf)
     For j = 0 To UBound(sp)
        If Not sp(j) = "" Then
           sq = Asc(Left(LTrim(sp(j)), 1))
            If sq < 65 Or sq > 90 And sq < 97 Or sq > 122 Then
                ar(i, 2) = ar(i, 2) & LTrim(sp(j)) & vbLf
            End If
            If j = UBound(sp) And Right(ar(i, 2), 1) = vbLf Then ar(i, 2) = StrReverse(Replace(StrReverse(ar(i, 2)), vbLf, "", 1, 1))
       End If
     Next
 Next
 
 Range("C1", Range("C" & Rows.Count).End(xlUp)) = Application.Index(ar, 0, 2)
End Sub

 

[JEC]

A slightly different approach with an extra array. In this case it doesn't matter if Column D is empty or not

Sub jec()
 Dim ar, jv, sp As Variant, sq As Long, i As Long, j As Long
 ar = Range("C1", Range("C" & Rows.Count).End(xlUp))
 ReDim jv(1 To UBound(ar))
 
 For i = 1 To UBound(ar)
     sp = Split(ar(i, 1), vbLf)
     For j = 0 To UBound(sp)
        If Not sp(j) = "" Then
           sq = Asc(Left(LTrim(sp(j)), 1))
            If sq < 65 Or sq > 90 And sq < 97 Or sq > 122 Then
                jv(i) = jv(i) & LTrim(sp(j)) & vbLf
            End If
            If j = UBound(sp) And Right(jv(i), 1) = vbLf Then jv(i) = StrReverse(Replace(StrReverse(jv(i)), vbLf, "", 1, 1))
       End If
     Next
 Next
 
 Range("C1", Range("C" & Rows.Count).End(xlUp)) = Application.Transpose(jv)
End Sub

 

[jiobi]

Nope, it is not working sir.
Showing issue in this line: Range("C1", Range("C" & Rows.Count).End(xlUp)) = Application.Transpose(jv)

 

[JEC]

Can you post some sample data? I tried it with my own sample data and it worked properly.

You probably have empty cells or cells without multiple lines

 

[jiobi]

Also if I can delete the lines with "[" if anywhere then it will also fulfill my need because it will delete the lines in cells starting with an alphabet. Yes, there is space between short lines.

 

[JEC]

If you could post some sample data I can look again

 

[jiobi]

Here is a sample data: Upload files for free - sample data.txt - ufile.io where the code is not working if found in cell.

 

[JEC]

Can't download this, you can post some sample data here as table or XL2BB

 

[Joe4]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.