How to remove [TAG 001], [TAG 002], [TAG 003], etc from string

[swaggerbox]

I need to remove all instances of [TAG 001], [TAG 002], [TAG 003], etc. from a string. How do I do that using VBA?

[KOKOSEK]

Maybe something 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




1

The 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




1

The quick [TAG 001] brown fox [TAG 002] jumps [TAG 003] over the [TAG 004] lazy [TAG 005] dog
=RemoveTAGs(A1)




Sheet: Sheet1

[大灰狼1976]

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

[swaggerbox]

What if it iterates to 10 or more, e.g. [TAG 010], [TAG 011] etc.?

[paulked]

Here's a guess... use Wolfies code but with [TAG 0??] and if you are going into the hundreds [TAG ???]

[大灰狼1976]

Hi paulked!
Thank you for explaining for me.
Yes, I think so, too.

[paulked]

:thumb

[大灰狼1976]

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

sub test
columns("a").replace "[TAG 0??]", ""
end sub


But if want to process strings, it need to use other methods.
KOKOSEK's method is OK, and can also use the regExp method.

[KOKOSEK]

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.


The quick [TAG 001] brown fox [TAG 002] jumps [TAG 003] over the [TAG 004] lazy [TAG 005] dog
5 TAGs so:


For i=1 to 5

and also what if >9 TAGs.

OR

use other way (regExp is probably best method).
I am not strong enough with regExp.

not so bad. With regExp:



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




should works (of course due to {3} parameter from TAG 001 to TAG 999)

[paulked]

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

[大灰狼1976]

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

[swaggerbox]

Thanks a lot guys. Plenty of help.

[snb]

Why didn't you post a representative example ?
Now I see see only guesses.