PDA

View Full Version : How to remove [TAG 001], [TAG 002], [TAG 003], etc from string



swaggerbox
11-14-2019, 03:53 AM
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
11-14-2019, 04:31 AM
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] dogThe 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

大灰狼1976
11-14-2019, 04:41 AM
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
11-14-2019, 04:54 AM
What if it iterates to 10 or more, e.g. [TAG 010], [TAG 011] etc.?

paulked
11-14-2019, 05:01 AM
Here's a guess... use Wolfies code but with [TAG 0??] and if you are going into the hundreds [TAG ???]

大灰狼1976
11-14-2019, 05:14 AM
Hi paulked!
Thank you for explaining for me.
Yes, I think so, too.

paulked
11-14-2019, 05:40 AM
:thumb

大灰狼1976
11-14-2019, 06:49 AM
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
11-14-2019, 07:10 AM
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
11-14-2019, 08:15 AM
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
11-14-2019, 06:56 PM
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
11-15-2019, 02:24 AM
Thanks a lot guys. Plenty of help.

snb
11-15-2019, 03:09 AM
Why didn't you post a representative example ?
Now I see see only guesses.