Help to remove specific named duplicate word from a cell

[Cruiser69]

Hi all.
I have searched everywhere I can and cannot find an answer.
Below is an example of what I am trying to achieve.
When multiple items are scanned into a sale sheet I run a macro which merges them together.
I want to remove the multiple "TO INCLUDE" words, just leaving the first instance as in Row 7.
The normal remove duplicates will remove all duplicate words, but as there are multiple sideboards as in the example, it would remove those as well.

Any help will be appreciated.

Regards,

Graham

 

[Peter_SSs]

It may not matter to you but the earlier formula puts a double space in the text in the first two results.
In any case, here is a simpler formula that I think also does the job required

=LET(ti," TO INCLUDE",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,ti," #",1),ti,"")," #",ti))

.. and the equivalent vba

Sub ReduceToInclude()
  With Range("E2", Range("E" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate("LET(ti,"" TO INCLUDE"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & .Address & ",ti,"" #"",1),ti,""""),"" #"",ti))")
  End With
End Sub

 

[Cruiser69]

It may not matter to you but the earlier formula puts a double space in the text in the first two results.
In any case, here is a simpler formula that I think also does the job required

=LET(ti," TO INCLUDE",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,ti," #",1),ti,"")," #",ti))

.. and the equivalent vba

Sub ReduceToInclude()
  With Range("E2", Range("E" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate("LET(ti,"" TO INCLUDE"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & .Address & ",ti,"" #"",1),ti,""""),"" #"",ti))")
  End With
End Sub

Thanks Peter.

I'll have a look.

Regards,

Graham