I need to remove all instances of [TAG 001], [TAG 002], [TAG 003], etc. from a string. How do I do that using VBA?
I need to remove all instances of [TAG 001], [TAG 002], [TAG 003], etc. from a string. How do I do that using VBA?
Maybe smoething like:
Function RemoveTAGs(InputStr As String) As String Dim i As Long Dim tempStr As String tempStr = InputStr For i = 1 To 9 tempStr = Replace(tempStr, "[TAG 00" & i & "]", "") Next i RemoveTAGs = tempStr End Function
A B 1The quick [TAG 001] brown fox [TAG 002] jumps [TAG 003] over the [TAG 004] lazy [TAG 005] dog The quick brown fox jumps over the lazy dog
A B 1The quick [TAG 001] brown fox [TAG 002] jumps [TAG 003] over the [TAG 004] lazy [TAG 005] dog =RemoveTAGs(A1)
Sheet: Sheet1
Hello!
Maybe a wildcard would be better.
sub test dim s$ s = "The quick [TAG 001] brown fox [TAG 002] jumps [TAG 003] over the [TAG 004] lazy [TAG 005] dog" debug.print replace(s, "[TAG 00?]", "") end sub
What if it iterates to 10 or more, e.g. [TAG 010], [TAG 011] etc.?
Here's a guess... use Wolfies code but with [TAG 0??] and if you are going into the hundreds [TAG ???]
Semper in excretia sumus; solum profundum variat.
Hi paulked!
Thank you for explaining for me.
Yes, I think so, too.
Semper in excretia sumus; solum profundum variat.
Sorry to swaggerbox, KOKOSEK and paulked
When I was taking a shower, I realized that I had made a low-level mistake.
Wildcards are not supported for the replace() function but the range.replace method.
So, If the string is in column a, it can be replaced directly like below
But if want to process strings, it need to use other methods.sub test columns("a").replace "[TAG 0??]", "" end sub
KOKOSEK's method is OK, and can also use the regExp method.
Question is: is TAG 010 can exist without TAG 009, 008, 007 etc.
If not we can count how many TAGs we've got then create loop from 1 to 'TAG.Count'.
For ex.
5 TAGs so:The quick [TAG 001] brown fox [TAG 002] jumps [TAG 003] over the [TAG 004] lazy [TAG 005] dog
and also what if >9 TAGs.For i=1 to 5
OR
use other way (regExp is probably best method).
I am not strong enough with regExp.
not so bad. With regExp:
should works (of course due to {3} parameter from TAG 001 to TAG 999)Function RemoveTAGs(InputStr As String) As String Dim i As Long Dim tempStr As String Dim regTag As Object Set regTag = CreateObject("VBScript.RegExp") tempStr = InputStr regTag.Global = True regTag.Pattern = "\[TAG\s[0-9]{3}\]\s" RemoveTAGs = regTag.Replace(tempStr, "") End Function
Last edited by KOKOSEK; 11-14-2019 at 07:26 AM. Reason: regExp added
And another way, relying on square brackets only being used in [TAG ???]!Sub ked() Dim str As String, arr As Variant, newstr As String, i As Long str = "The quick [TAG 001] brown fox [TAG 002] jumps [TAG 003] over the [TAG 004] lazy [TAG 005] dog" arr = Split(str, " ") For i = 0 To UBound(arr) If Not Left(arr(i), 4) = "[TAG" And Not Right(arr(i), 1) = "]" Then newstr = newstr & arr(i) & " " Next newstr = Left(newstr, (Len(newstr) - 1)) MsgBox newstr End Sub
Semper in excretia sumus; solum profundum variat.
If the number is large (for example, 1-100000), it is more efficient to use regularization than for...next.
If it is to remove [TAG ???] directly in the worksheet, I still insist that it is easier to use range.replace method + wildcard.
Here is a regular UDF example:
Function regReplace(s$) Dim reg As Object Set reg = CreateObject("vbscript.regexp") With reg .Pattern = "\[TAG \d+\]" .Global = True regReplace = Application.Trim(.Replace(s, "")) End With End Function
Thanks a lot guys. Plenty of help.
Why didn't you post a representative example ?
Now I see see only guesses.