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

 

[I_know_nuffin]

to create in a different cell use
=LEFT(E2,(FIND("TO INCLUDE ",E2,1)+10)) & SUBSTITUTE(MID(E2, (FIND("TO INCLUDE ",E2,1)+10), LEN(E2)),"TO INCLUDE ","")

 

[Cruiser69]

Thanks for your quick reply.
This is just what I needed.

Would there be a way to incorporate this into vba.

If not, this will be fine.
.
Regards,

Graham

 

[I_know_nuffin]

I think it just need substitute changing to replace to work in VBA

and find changing to instr

 

[I_know_nuffin]

if str contains the whole string

Left(str, InStr(1, str, "TO INCLUDE") + 10) + Replace(Mid(str, InStr(1, str, "TO INCLUDE") + 10, Len(str)), "TO INCLUDE ", "")

 

[Cruiser69]

Hi again.

This works well with merged cells, but if it is a single item I get an #VALUE
Is there an easy fix for this.

I would like to try the vba approach, but I'm not sure how to add it to a module.


Regards,

Graham.

 

[I_know_nuffin]

to get rid of the #Value
IFERROR(LEFT(E2,(FIND("TO INCLUDE ",E2,1)+10)) & SUBSTITUTE(MID(E2, (FIND("TO INCLUDE ",E2,1)+10), LEN(E2)),"TO INCLUDE ",""),E2)

 

[I_know_nuffin]

the below is quick and dirty but does what you need

Sub removestring()

Dim str As String, str1 As String

' make sure your on the correct sheet in excel
' sheets("Sheetname").select
Sheets("Sheet1").Select
Range("E2").Select
Do While ActiveCell <> ""

str = ActiveCell.Value
If InStr(1, str, "TO INCLUDE") > 0 Then
str1 = Left(str, InStr(1, str, "TO INCLUDE") + 10) + Replace(Mid(str, InStr(1, str, "TO INCLUDE") + 10, Len(str)), "TO INCLUDE ", "")
str1 = Mid(str, InStr(1, str, "TO INCLUDE") + 10, Len(str))
' add new string to sheet in column F if it inclues search string - TO INCLUDE
ActiveCell.Offset(0, 1) = str1
Else
' add original string to sheet in column F if it doesnt inclues search string - TO INCLUDE
ActiveCell.Offset(0, 1) = str
End If
' move to next cell down
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[Cruiser69]

This is perfect.

Thanks for all your replies

Regards,

Graham

 

[I_know_nuffin]

Glad it worked for you