Forum Discussion

TheSandman1290's avatar
TheSandman1290
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
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Aug 28, 2022

    TheSandman1290 

    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
HansVogelaar's avatar
HansVogelaar
MVP
Aug 28, 2022

TheSandman1290 

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
  • TheSandman1290's avatar
    TheSandman1290
    Copper Contributor
    Aug 28, 2022

    HansVogelaar 

     

    Last question, can this vbasic be easily adapted to do the same but also remove the last 6 lines in the multiline cells?

    So remove top 13 and last 6 lines of each multiline cell, is it possible to do this in one and the same script?

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Aug 28, 2022

      TheSandman1290 

      Perhaps a good idea to learn about PowerQuery anyway. One extra line of code will fix it.

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

       File attached.

      The site in the link below would be a good place to start with PQ.

      https://exceloffthegrid.com/power-query-introduction/ 

      • TheSandman1290's avatar
        TheSandman1290
        Copper Contributor
        Aug 28, 2022
        yep, looks like i need to dig into this! Many thanks Riny 😉
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 28, 2022

      TheSandman1290 

      Yep, but it might run slowly:

      Sub RemoveFirst13AndLast6Lines()
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("B" & Rows.Count).End(xlUp).Row
    Range("C1").EntireColumn.Insert
    Range("C1:C" & m).Formula = _
        "=IFERROR(MID(B1,FIND(CHAR(164),SUBSTITUTE(B1,CHAR(10),CHAR(164)," & _
            "13))+1,FIND(CHAR(164),SUBSTITUTE(B1,CHAR(10),CHAR(164)," & _
            "LEN(B1)-LEN(SUBSTITUTE(B1,CHAR(10),""""))-5))-FIND(CHAR(164)," & _
            "SUBSTITUTE(B1,CHAR(10),CHAR(164),13))-1),"""")"
    Range("B1:B" & m).Value = Range("C1:C" & m).Value
    Range("C1").EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub
      • TheSandman1290's avatar
        TheSandman1290
        Copper Contributor
        Aug 28, 2022
        Hi Hans,
        thanks but that seems to delete everything in the multiline cells of my column, unless i did not copy it over correctly? ( or unless it runs reaaally slowly?)
  • TheSandman1290's avatar
    TheSandman1290
    Copper Contributor
    Aug 28, 2022
    Bedankt Hans, dit doet exact wat ik wou!