Forum Discussion

Copper Contributor

Aug 28, 2022

Solved

remove first 13 lines from multiline cells

Hi everyone, this is my first post here! I am looking for a way to remove the first 13 lines of a column (B) that is filled with multi-line cells Usually my google-fu is strong, but for this...

excel

Formulas and Functions

Aug 28, 2022

Sub RemoveFirst13Lines()
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("B" & Rows.Count).End(xlUp).Row
    Range("C1").EntireColumn.Insert
    Range("C1:C" & m).Formula = _
        "=MID(B1,FIND(CHAR(164),SUBSTITUTE(B1,CHAR(10),CHAR(164),13))+1,10000)"
    Range("B1:B" & m).Value = Range("C1:C" & m).Value
    Range("C1").EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

Riny_van_Eekelen

Platinum Contributor

Aug 28, 2022

TheSandman1290 Alternatively, use PowerQuery.

Transform the column to a named range or structured table. Connect to it with PQ and perform one simple step that extracts everything after the 12th line feed that it finds in each cell of the range/table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Extract = Table.TransformColumns(Source, {{"Text", each Text.AfterDelimiter(_, "#(lf)", 12), type text}})
in
    Extract

Example file attached. Sheet1 contains a table with 37 "multi-line" cells. The Table2 sheet contains the PQ output.

Edit: Or, for Insiders the TEXTAFTER function achieves the same:

=TEXTAFTER(Table2[Text],CHAR(10),13)

sandman.xlsx18 KB

TheSandman1290

Copper Contributor

Aug 28, 2022

Hi Riny,

Many thanks too you too for your quick anwser, i will need to read up a little more how to exactly apply your solution, as i am rather beginner level in excel, through google i found out about the visual basic through developer so i could test first solution right away and that already seems to do the trick. I will look into you solution too as it's always good to have more then one way of doing things! 🙂