VBA remove a word before Multiple Specific text

[decadence]

Hi, I am trying to delete the length of a word if multiple specific text is found in a cell, but the code I have deletes the whole string rather than just the length of the word which is variable, Can someone help.

    Dim c As range
    For Each c In Selection
        If InStr(c.value, " Pie") > 0 Then
            c.value = Left(c.value, InStr(c.value, " Pie") - 1)
        End If
            If InStr(c.value, "Pie") > 0 Then
         c.value = Left(c.value, InStr(c.value, "Pie") - 1)
        End If
        If InStr(c.value, " Split") > 0 Then
            c.value = Left(c.value, InStr(c.value, " Split") - 1)
        End If
        If InStr(c.value, "Split") > 0 Then
         c.value = Left(c.value, InStr(c.value, "Split") - 1)
        End If
    Next c
End Sub

Example:

The multiple specific text I am trying to find is Pie and Split

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Something MyApple Pie Something[/TD]
[/TR]
[TR]
[TD]Something YourApple Pie Something[/TD]
[/TR]
[TR]
[TD]Something TheirApplePie Something[/TD]
[/TR]
[TR]
[TD]Something Banana Split Something[/TD]
[/TR]
[TR]
[TD]Something BananasSplit Something[/TD]
[/TR]
</tbody>[/TABLE]


Then Delete the Length of the word before the text found

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Something Something[/TD]
[/TR]
[TR]
[TD]Something Something[/TD]
[/TR]
[TR]
[TD]Something Something[/TD]
[/TR]
[TR]
[TD]Something Something[/TD]
[/TR]
[TR]
[TD]Something Something[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG11Dec48
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] sp [COLOR="Navy"]As[/COLOR] Variant, nSp [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Const Str1 = "Pie"
Const Str2 = "Split"
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        nStr = ""
        sp = Split(Dn.Value, " ")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(sp)
            [COLOR="Navy"]If[/COLOR] InStr(sp(n), Str1) > 0 [COLOR="Navy"]Then[/COLOR]
                    nStr = IIf(Len(sp(n)) = Len(Str1), sp(n - 1) & "," & sp(n), sp(n))
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] InStr(sp(n), Str2) > 0 [COLOR="Navy"]Then[/COLOR]
                nStr = IIf(Len(sp(n)) = Len(Str2), sp(n - 1) & "," & sp(n), sp(n))
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
 
        nSp = Split(nStr, ",")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(nSp)
            sp = Filter(sp, nSp(n), False, 1)
        [COLOR="Navy"]Next[/COLOR] n
        Dn.Value = Join(sp, " ")
 [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[decadence]

Hi MickG, I'm getting Run-time error '9': Subscript out of Range after the word First word has been removed.

                    nStr = IIf(Len(sp(n)) = Len(Str1), sp(n - 1) & "," & sp(n), sp(n))

 

[MickG]

I should imagine One of your key words is at the start of a cell string
Can you post the Actual data with expected results.

The code should deletes the word with the Key word in it, but if key word it is a single word (not part of a bigger word) , then it should delete that keyword and the word before it. If the key word happens to be the first word, then that creates a problem for the code, If that is the case with your error, what do you want to do in those circumstances.

 

[decadence]

My data is not allowed to be shared I'm afraid, Yes it does delete the word with key word but after it has been deleted that is when it errors on the Str1 word.
However I changed the set range to selection and it seems to work o.k. The key word will not be the first word in the string as it is always located roughly in the middle of the string.

 

[MickG]

If its working I should go with that.
NB:- You didn't need to send the actual data line it failed on, only an example of it structure.
Regrds Mick

 

[decadence]

Ok Thank you MickG, Thank you for your help.

Is it possible to do the same thing but using only 1 specific letter but this time all values before the keyword are numerical so for example:

Something 12345f Something or Something 1.239p Something

 

[MickG]

Do you want the result to look like this ???:-

Something Something or Something Something

 

[decadence]

Yes I would please

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG11Dec30
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] sp [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nSp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        nStr = ""
        sp = Split(Trim(Dn.Value), " ")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(sp)
            [COLOR="Navy"]If[/COLOR] IsNumeric(Left(sp(n), Len(sp(n)) - 1)) And Right(sp(n), 1) Like "[a-z]" [COLOR="Navy"]Then[/COLOR]
                    nStr = nStr & IIf(nStr = "", sp(n), "," & sp(n))
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
        nSp = Split(nStr, ",")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(nSp)
            sp = Filter(sp, nSp(n), False, 1)
        [COLOR="Navy"]Next[/COLOR] n
        Dn.Value = Join(sp, " ")
 [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick