How to delete text between two specific words in cell?

[ea2146]

Hello,

I am trying to create an excel function that will identify two specific words embedded in messages of varying lengths (that I scraped from a message board online) and delete all the text in between those two words.

Specifically, the message board that I scraped the data from allowed users to quote other users and then respond to those quoted messages. The quoted text is always presented in the following way:



PREVIOUS MESSAGE AUTHOR Username said:

Message written by the previous author

Click to expand...

Message written by the CURRENT MESSAGE AUTHOR


Here is what it looks like in excel:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]User235235 said:


This is the text I need deleted.

Click to expand...


This is the text I want to save.[/TD]
[/TR]
[TR]
[TD]User8888986655 said:


This is the text I need deleted. Blah blah Blah blah Blah blah Blah blah

Click to expand...


This is the text I want to save. Blah blah Blah blah Blah blah[/TD]
[/TR]
[TR]
[TD]User2222222222 said:


This needs to be deleted.

Click to expand...


Save this please[/TD]
[/TR]
</tbody>[/TABLE]

To complicate things further, sometimes there are more than one quoted messages from a previous author and the words "said:" and "Click to expand..." appear more than once in that case.
Therefore, I need an excel function that will identify the words " said:" and "Click to expand..." and delete all the words in between those two phrases (as well as those two words).

Any suggestions would be greatly appreciated. Thanks so much in advance!!

 

[sandy666]

with PowerQuery (Get&Transform):

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]raw.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]User235235 said:


This is the text I need deleted.

Click to expand...


This is the text I want to save.[/td][td][/td][td=bgcolor:#E2EFDA]This is the text I want to save.[/td][/tr]

[tr=bgcolor:#FFFFFF][td]User8888986655 said:


This is the text I need deleted. Blah blah Blah blah Blah blah Blah blah

Click to expand...


This is the text I want to save. Blah blah Blah blah Blah blah[/td][td][/td][td]This is the text I want to save. Blah blah Blah blah Blah blah[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]User2222222222 said:


This needs to be deleted.

Click to expand...


Save this please[/td][td][/td][td=bgcolor:#E2EFDA]Save this please[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "raw", Splitter.SplitTextByEachDelimiter({"#(lf)"}, QuoteStyle.Csv, true), {"raw.1", "raw.2"}),
    ROC = Table.SelectColumns(Split,{"raw.2"})
in
    ROC[/SIZE]

 

[pinarello]

Same idea as Sandy, but a little bit more.

Link to my solution in OneDrive

 

[ea2146]

Thank you very much! I'm sorry to be so ignorant, but can you say more about how I enter this code into PowerQuery to produce the end result in column 2? I understand that the Get&Transform commands are under the data tab, but I am not sure what to do from there. Thanks again!

 

[Nishant94]

Formula version in case you need it:

Book1
	A	B
1	User235235 said: This is the text I need deleted. Click to expand... This is the text I want to save.	This is the text I want to save.
2	User8888986655 said: This is the text I need deleted. Blah blah Blah blah Blah blah Blah blah Click to expand... This is the text I want to save. Blah blah Blah blah Blah blah	This is the text I want to save. Blah blah Blah blah Blah blah
3	User2222222222 said:This needs to be deleted.Click to expand...Save this please	Save this please
4	User2222222222 said: This needs to be deleted. Click to expand User 1456 Said: This needs to be deleted. Click to expand... Save this please	Save this please
Sheet3

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10),""),"Click to expand...",REPT(" ",LEN(A1))),LEN(A1)))

 

[ea2146]

Excellent! This is perfect. Thank you all very much for your helpful responses. Much appreciated!

 

[DanteAmor]

How about:

=TRIM(RIGHT(SUBSTITUTE(A2,"Click to expand...",REPT(" ",250)),250))

 

[sandy666]

how I enter this code into PowerQuery to produce the end result in column 2? I understand that the Get&Transform commands are under the data tab, but I am not sure what to do from there.

select your source data and use ctrl+t
change header to "raw"
check in name manage the name of the table
select any cell in this table and in Get&Transform find From Table (depend of the Excel version) and use it
it will open PowerQuery Editor, find Advanced Editor - open - paste code from the post (replace all there with the new code)
check table name and adjust if necessary then close PQ Editor
Close&Load to
1.Table
2.a) new worksheet
b) existing worksheet - select cell

remember that Power Query is case sensitive so raw is not the same as RAW or Raw