Forum Discussion
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...
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
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?
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
Extract1File attached.
The site in the link below would be a good place to start with PQ.
- yep, looks like i need to dig into this! Many thanks Riny 😉