Formula to find multiple occurrences of string within brackets

[pselva01]

Hello,

I have a column of data containing special character codes within brackets. Each cell may contain more than once occurrence of a bracketed code. For example:

Cell D2:
CARSTA K[OUML]HLER, J[UUML]RGEN M[UUML]LLER, IRIS KLEINE, ANDREAS PF[UUML]TZNER

I need a formula to return all occurrences of the bracketed code within the cell. I have the following formula that works, but only returns one instance within a cell and I need to find them all.

=MID(D2,FIND("[",D2)+1,FIND("]",D2)-FIND("[",D2)-1)

Any guidance would be appreciated!

Thanks!

 

[CharlesChuckieCharles]

Why do you want to find them?

 

[pselva01]

I will use the extracted the data to update a script to find/replace the bracketed string with the correct special characters.

 

[CharlesChuckieCharles]

I will use the extracted the data to update a script to find/replace the bracketed string with the correct special characters.

This is a bit of code that replaces all occurances of a character in a cell with another

it acts on a selection as in select somes Cells and run macro

( this replace [ or ] with "/" in this example)

Public Sub CleanofReturns()
Dim c As Range
'ActiveSheet.UsedRange.Select
For Each c In Selection
    If (Not IsNumeric(c.Value)) And (Not c.HasFormula) Then
 
        c.Value = Replace((c.Value), "[", "/")
        c.Value = Replace((c.Value), "]", "/")
    End If
 
Next c
End Sub

edit as necessary

 

[pselva01]

Thank you! This macro will definitely be useful, but isn't quite what I need at the moment.

The script is for a different program--not Excel--and my imported data isn't coming through correctly. I don't need to find/replace the instances in Excel itself; I just need to find the strings so I can figure out what codes aren't being replaced and then update my external script accordingly.

 

[CharlesChuckieCharles]

Since no else has answered

try this

quick and dirty

Public Function XXstuff(meme As Range) As String
Dim x As Long
Dim y As Long
Dim z As Boolean
Dim stemp As String
Dim stemp2 As String
On Error Resume Next
stemp = meme
stemp2 = ""
z = False
XXstuff = stemp
While Not z
x = InStr(1, stemp, "[")
y = InStr(1, stemp, "]")
    If x = 0 Or y = 0 Then
       z = True
    Else
        stemp2 = stemp2 & Mid$(stemp, x, (y - x) + 1)
        stemp = Replace(stemp, Mid$(stemp, x, (y - x) + 1), "", 1, 1)
    End If
Wend

If stemp2 <> "" Then
    XXstuff = stemp2
End If

End Function

use in sheet =xxstuff(d2)