Remove repeated words in a string with RegEx Find and Replace?

[kloruklass]

I've always been using this VBA code so far: =RemoveDupes2(B1,"_")

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
    Dim x
    'Updateby Extendoffice
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
    End With
End Function

Everything works great, but I was looking for a RegEx expression to use with a renamer like BRU Utility to save time.
Plus it's limited due to the "_" which is used to read words in between, so I'm forced to rename everything first adding the "_" everywhere.
While as you can see, I have lots of other characters: _ ( ) - [ ] { } = *space*

For instance, I'd like to change:

dmg_sm0993_event (summonedtable) 0039-event [damage=hit_defend_fail_se_0039_type=slash_low] [damage_fail_type=defend_fail] {!} {r} {m} {s} {e}

To (both "{}" and characters inside are not needed.)

dmg_sm0993_event_summonedtable_0039_damage_hit_defend_fail_se_type_slash_low

 

[Domenic]

Try the following custom function (or user-defined function) . . .

Function ExtractUniqueExceptWithinBraces(txt As String, Optional sep As String = "_") As String

    Dim oRegExp As Object
    Dim oMatches As Object
    Dim oMatch As Object
    Dim oDic As Object
    Dim temp As String
   
    Set oDic = CreateObject("Scripting.Dictionary")
    oDic.CompareMode = vbTextCompare
   
    Set oRegExp = CreateObject("VBScript.RegExp")
   
    'remove text within braces along with the braces themselves
    With oRegExp
        .Global = True
        .Pattern = "\{.*?\}"
        txt = .Replace(txt, "")
    End With
   
    'match remaining text
    With oRegExp
        .Pattern = "[a-zA-Z0-9]+"
        Set oMatches = .Execute(txt)
    End With
   
    'loop through each match and concatenate the unique matches
    temp = ""
    For Each oMatch In oMatches
        If Not oDic.exists(oMatch.Value) Then
            temp = temp & sep & oMatch.Value
            oDic(oMatch.Value) = ""
        End If
    Next oMatch
   
    ExtractUniqueExceptWithinBraces = Mid(temp, 2)
   
End Function

Your worksheet formula would be as follows . . .

=ExtractUniqueExceptWithinBraces(A1,"_")

or

=ExtractUniqueExceptWithinBraces(A1)

Hope this helps!

 

[kloruklass]

OMG thank you so much!!!

 

[kloruklass]

I realized I've come up with another tedious issue.

I'm not sure if it's possible to do everything at once (even though that would be just awesome).
But after removing the unnecessary characters and repeated words, I'd like to add space between numbers and capital letters, but ignore if they're consecutive.
Single numbers like 1, 2, 3 would have a "0" in front of it, but if there's already a "0" so ignore it.
Then replace the spaces with an underscore.

For instance:

VO_WildRiver01_ISLAND4_TreasureChest_NPC2

To

VO_Wild_River_01_ISLAND_04_Treasure_Chest_NPC_02

Thanks a lot for helping.

 

[Peter_SSs]

I'd like to add space between numbers and capital letters,

Just checking on the underlined part. below you have added between loer case and number

VO_Wild_River_01_ISLAND_04_Treasure_Chest_NPC_02


Also, could the letter be after the number, like this, without a space and would that require one to be added?

ISLAND_4TreasureChest

 

[kloruklass]

Yeah, I would like it to add the space and underscore, otherwise, it's a little confusing when you're going to read the names.
One thing that I forgot to mention is to leave alone the single characters with the capital letter and the numbers+capital letter together.

From

VO_WildRiver01_ISLAND4_TreasureChest_NPC2_L_R_3D

To

VO_Wild_River_01_ISLAND_04_Treasure_Chest_NPC_02_L_R_3D