Excel VBA Complex String Replace

3
votes

Is there a way to find a sub-string in a string then replace that sub-string and a number of characters before and after it?

For example, I have these:

Mandatory659852 y n n TRY RACE
Mechanica888654 y n n FRY SAME
GILLETE685254 y n n CRY FACE

Can I program VBA to remove the sub-string " y n n " including whatever 6 characters to the left of the sub-string and whatever 3 characters to the right of it? So the result should be:

"Mandatory RACE"
"Mechanica SAME"
"GILLETE FACE"

I couldn't get a search on Google as I have no clue what the term is. I know I can use .replace in VBA to replace sub-strings but I do not know how to use wild cards by number of character like .replace(****** y n n ***)

3
stringvbaexcel
Do you need VBA? I think you could do this, with some uses of Mid() and Search(). Edit: Here's a quick, probably too easy, way: =LEFT(A1,SEARCH(" ",A1))&RIGHT(A1,4). And if you need it in VBA, just "translate" it to VBA. This assumes your last word is always 4 letters...if it's more, you can tweak. - BruceWayne
I prefer VBA to worksheets because on the long run I have less work but whether you do you manually or programmatically, I also wanted to ask if you wouldn't rather use Left() and Mid() or Find() (Search() if manually) - Bernd Stoeckel
Thanks guys, I know about Left() and Mid() but I'm looking for a way to do this with less and simpler coding as possible. Left() and Mid() were my first option but then I remember seeing a solution like this before, I just couldn't remember the term or name of that solution.:) I need to find a solution through VBA for other reasons. - jay

3 Answers

10
votes

VBA:

You use ? to match a single wildcard character.

Just highlight the cells and run this macro.

You can also just use this in the Find/Replace dialog.

Sub FixIt()
Selection.Replace "?????? y n n ???", "", xlPart
End Sub

Results

3
votes

I used a formula as you have quite a common format. If the value is in A1, then in B1 try:

=LEFT(A1,SEARCH("y n n",A1)-8) & RIGHT(A1,LEN(A1)-(SEARCH("y n n",A1)+8))

Excel results

Of course this depends on you always having a very strict format with 6 digits followed by a space followed by a "y n n" followed by a space, followed by a 3 letter word, followed by a space and then something else!!

1
votes

Something like this, in line with BruceWayne, instr is the search and we're using mid, both available as WS functions

Dim sINPUT  As String
    Dim sOUTPUT As String

    sINPUT = "Mandatory659852 y n n TRY RACE"

    sOUTPUT = Mid(sINPUT, 1, (InStr(1, sINPUT, " y n n") - 6) - 1)
    sOUTPUT = sOUTPUT & Mid(sINPUT, 3 + (InStr(1, sINPUT, "y n n") + 1 + Len("y n n")))        
    Debug.Print sOUTPUT