Forum Discussion
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, not so much !
So again, i have lets say column B that contains 20 rows of all multiline cells, from each of these multiline cells, i just want to remove the first 13 lines and keep the rest of the lines of that cell in there as they were.
I am using office 365
Hope anyone here can shed some light on how i can achieve 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
11 Replies
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 ExtractExample 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)- 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! 🙂
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 SubLast 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.
- Bedankt Hans, dit doet exact wat ik wou!
