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]

 

[decadence]

Hi Fennek, I know of RegEx: but no idea where to start with this.

 

[MickG]

Running both codes returned the correct answer.
I've altered the code in line below for just numbers ending in "p"

If IsNumeric(Left(sp(n), Len(sp(n)) - 1)) And Right(sp(n), 1) = "p" Then

You could try replacing that data in the file I send , alter the "p" bit, see what happens ??

 

[Fennek]

Hi,

at the moment I can't test it, so the code is from my archive (a bit adopted)

Function catchNumeric(TextValue As String) As string
    Dim regex As Object
    Set regex = CreateObject("VBscript.regexp")
    With regex
        .Pattern = "\s\d+p\s"
        If .test(TextValue) Then
        catchNumeric = .Replace(TextValue, "")
    End If
    End With
End Function

regards

 

[MickG]

Fennek, your code worked for me as below:-

[COLOR="Navy"]Sub[/COLOR] MG12Dec03
[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
    [COLOR="Navy"]Dim[/COLOR] objRegex
    [COLOR="Navy"]Set[/COLOR] objRegex = CreateObject("vbscript.regexp")
    [COLOR="Navy"]With[/COLOR] objRegex
      .Global = True
      .Pattern = "\s\d+p\s"
      Dn.Value = .Replace(Dn.Value, " ")
    [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[decadence]

Hi MickG, Changing that one line has worked perfectly. Hi Fenneck, Your RegEx code works perfectly aswell, Thank you both for taking time to help me with this. It's much appreciated

 

[MickG]

You're welcome