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 MickG, the code doesn't seem to do anything. If possible can the search be on specific letters like f and p for example but with a space as what I am searching will always have a space after the letter but will always have numbers before the letter. it would be much appreciated.

 

[MickG]

I've altered the code slightly, it seems to be working.
I've also included an example file below:-

Code:- Based on Data in Column "A"

[COLOR="Navy"]Sub[/COLOR] MG12Dec04
[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
       [COLOR="Navy"]If[/COLOR] nStr <> "" [COLOR="Navy"]Then[/COLOR]
            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"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

https://app.box.com/s/neojunckljzqurivhjrt4mygg7fth2v8

Regards Mick

 

[decadence]

On the File attached yes it seems to be working but on my data it doesn't seem to do anything, I'm not sure why. It's really frustrating I can't send any data, The file attached is pretty similar to my data though

All I am trying to do is remove a variant of text in a cell which will always have the same letter but varied numbers before it without a space like " 12345p " or " 1.239p ", so if "p " is found in the cell then get the length of the numbers before "p " and replace "12345p " or "1.239p "with nothing.

 

[MickG]

Your file does not appear to have been sent !!!!

 

[decadence]

I have not sent a file due to security reasons. I apologize for the confusion, I meant that my data is similar to the file you attached.

 

[Fluff]

Sounds like your spaces may not be normal spaces. Has your data been copied from a website?

 

[decadence]

Hi Fluff, No my data is in house and I Trim the data to make sure everything is spaced correctly without hidden chars and special chars

 

[MickG]

You could try this simpler code, not sure it will make any difference.
Can you not take a line of your data that fails, modify some of the characters, to disguise it, but so it still fails and send that ????

[COLOR="Navy"]Sub[/COLOR] MG12Dec32
[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
       sp = Split(Trim(Dn.Value), " ")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(sp)
            [COLOR="Navy"]If[/COLOR] Not sp(n) = "" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] IsNumeric(Left(sp(n), Len(sp(n)) - 1)) And Right(sp(n), 1) Like "[a-z]" [COLOR="Navy"]Then[/COLOR]
                    Dn.Value = Replace(Replace(Dn.Value, sp(n), ""), "  ", " ")
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
   [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[decadence]

Hi MickG, The problem is nothing actually happens, it runs through the code but no data changes.

Below is a Snippet of what I actually use but modified (Approval from the higher uppers). This is why I was asking about finding the letter p.

(From This)
INDUCTOR SHIELDED POWER 8.2UH 500v 5030p 20% CASE 3030 COIL822MEPBXAL7031
INDUCTOR SHIELDED POWER 8.1UH 2v 7p 20% CASE 3030 COIL822MEPBXAL7030
INDUCTOR SHIELDED POWER 8.9UH 10v 30p 20% CASE 3030 COIL822MEPBXAL7035
INDUCTOR SHIELDED POWER 8.5UH 2.5v 990p 20% CASE 3030 COIL822MEPBXAL7038


(To This)
INDUCTOR SHIELDED POWER 8.2UH 500v 20% CASE 3030 COIL822MEPBXAL7031
INDUCTOR SHIELDED POWER 8.1UH 2v 20% CASE 3030 COIL822MEPBXAL7030
INDUCTOR SHIELDED POWER 8.9UH 10v 20% CASE 3030 COIL822MEPBXAL7035
INDUCTOR SHIELDED POWER 8.5UH 2.5v 20% CASE 3030 COIL822MEPBXAL7038

 

[Fennek]

Hi,

lookin just to the last post, what is about RegEx:

.Pattern = "\s\d+p\s"

.Replace(Tx, "")

untested

regards