CODEWITHSUNDEEP
excelvba
ptktoflow
ptktoflow

Reputation: 45

Remove Text Within Cell Starting with String and Ending with Character

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

Visual: enter image description here

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

Answers (1)

Michal
Michal

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

enter image description here

Upvotes: 1

Related Questions