Reputation: 45
I've been struggling trying to figure how to remove text that begins with some text and ends with a character:
I have a cell in Excel that could have a string:
[#1-Some text][#4-Some more text][#7-Some additional text]
OR
[#4-Some more text][#7-Some additional text]
I need to remove [#4-Some more text], but keep the remaining text
I want to use VBA to remove "[#4-Some More Text]", but that string could be different, the string will
I am creating the first part "[#4-" (because the number could change) programmatically and the snippet always end in a square bracket, the text in between could be different
I've tried tweaking multiple other posts: Remove text appearing between two characters - multiple instances - Excel Remove text that is between two specific characters of a string but can't seem to get it right.
Can someone please help or direct me to a similar situation?
Thank you!
Upvotes: 2
Views: 745
Reputation: 6153
Regext should help you out
Sub test()
Dim s As String
s = "[#1-Some text][#4-some more text][#7-Some additional text]"
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Pattern = "\[#4-[a-zA-Z ]+\]"
.ignorecase = True
Debug.Print .Replace(s, vbNullString)
End With
End Sub
Upvotes: 1