Excel VBA Complex String Replace

Question

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 ***)

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
– BruceWayne, Commented Dec 7, 2016 at 17:01
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
– Bernd Stoeckel, Commented Dec 7, 2016 at 17:03
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
– jay, Commented Dec 7, 2016 at 17:19

user1274820 · Accepted Answer · 2016-12-07 17:09:13Z

10

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

edited Dec 7, 2016 at 17:09

answered Dec 7, 2016 at 17:02

user1274820

8,1894 gold badges39 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BruceWayne Over a year ago

This is awesome, I never knew you could do that. (Also, the confirmation that's dragged into the view made me chuckle).

jay Over a year ago

There it is! :D Awesome! Sheet1.Range("B1").Value = Sheet1.Range("A1").Replace("?????? y n n ??? ", " ", xlPart, xlByRows, False). It is simpler and easy to code.

smackenzie · Accepted Answer · 2016-12-07 17:05:34Z

3

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))

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!!

answered Dec 7, 2016 at 17:05

smackenzie

3,0829 gold badges60 silver badges114 bronze badges

Comments

Nathan_Sav · Accepted Answer · 2016-12-07 17:01:31Z

1

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

answered Dec 7, 2016 at 17:01

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Collectives™ on Stack Overflow

Excel VBA Complex String Replace

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related