[퀴즈_67] 문자사이의 데이터 뽑아내기(신데렐라 돕기)

=LET(fX, LAMBDA(x,y,   IFERROR(
TEXTJOIN(", ",,DROP(WRAPROWS(TEXTSPLIT(x,,HSTACK(MID(y,1,1),MID(y,3,1))),2,""),,1)),"")),
 DROP(REDUCE("", C3:C102, LAMBDA(t,r, VSTACK(t, HSTACK(fX(r,E2), fX(r,F2), fX(r,G2))))),1))

=LET(f,LAMBDA(v,r,LET(a,MID(r,1,1),b,MID(r,3,1),
IFNA(TEXTJOIN(",",,REGEXREPLACE(REGEXEXTRACT(v,"\"&a&"(.*?)\"&b,1),"\"&a&"|\"&b,"")),""))),
l,C3:C102,h,E2:G2,
MAKEARRAY(ROWS(l),COLUMNS(h),LAMBDA(x,y,f(INDEX(l,x),INDEX(h,y)))))

'// 배열 처리 함수 - 범위 전체를 3열로 처리
Function Extract_Arr(rng As Range) As Variant
    On Error GoTo ErrH
   
    Dim oc As XlCalculation, os As Boolean, oe As Boolean
    oc = Application.Calculation
    os = Application.ScreenUpdating
    oe = Application.EnableEvents
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
       
    Static ch As Object, lmt As Long
    If ch Is Nothing Then
        Set ch = CreateObject("Scripting.Dictionary")
        lmt = 2000
    End If
    
    If rng Is Nothing Then GoTo ErrH
    
    Dim inp As Variant, out As Variant, rc As Long, i As Long
    Dim cv As String, ck As String, cr As Variant, tr(0 To 2) As String
    
    rc = rng.Rows.Count
    If rc > 100000 Then GoTo ErrH
    
    If rc = 1 Then
        ReDim inp(1 To 1, 1 To 1)
        inp(1, 1) = rng.Value
    Else
        inp = rng.Value
    End If
    
    ReDim out(1 To rc, 1 To 3)
    
    For i = 1 To rc
        If Not IsEmpty(inp(i, 1)) And Not IsError(inp(i, 1)) Then
            cv = CStr(inp(i, 1))
            
            If Len(cv) = 0 Then
                out(i, 1) = ""
                out(i, 2) = ""
                out(i, 3) = ""
            Else
                ck = cv
                If ch.Exists(ck) Then
                    cr = ch(ck)
                    out(i, 1) = cr(0)
                    out(i, 2) = cr(1)
                    out(i, 3) = cr(2)
                Else
                    tr(0) = ExtractBracket(cv, "[", "]")
                    tr(1) = ExtractBracket(cv, "{", "}")
                    tr(2) = ExtractBracket(cv, "(", ")")
                    
                    out(i, 1) = tr(0)
                    out(i, 2) = tr(1)
                    out(i, 3) = tr(2)
                    
                    If ch.Count < lmt Then
                        ch(ck) = tr
                    ElseIf ch.Count >= lmt Then
                        ClrCh ch, lmt \ 4
                        ch(ck) = tr
                    End If
                End If
            End If
        Else
            out(i, 1) = ""
            out(i, 2) = ""
            out(i, 3) = ""
        End If
    Next i
    
    Extract_Arr = out
    
    Application.Calculation = oc
    Application.ScreenUpdating = os
    Application.EnableEvents = oe
    Exit Function
    
ErrH:
    
    Application.Calculation = oc
    Application.ScreenUpdating = os
    Application.EnableEvents = oe
    
    If rc > 0 Then
        ReDim out(1 To rc, 1 To 3)
    Else
        ReDim out(1 To 1, 1 To 3)
    End If
    
    For i = 1 To UBound(out, 1)
        out(i, 1) = "Error"
        out(i, 2) = "Error"
        out(i, 3) = "Error"
    Next i
    
    Extract_Arr = out
End Function

'// 개별 괄호 추출 함수
Function ExtractBracket(txt As String, ob As String, cb As String) As String
    On Error GoTo ErrH
    
    '// 검증
    If Len(txt) = 0 Or Len(ob) = 0 Or Len(cb) = 0 Then
        ExtractBracket = ""
        Exit Function
    End If
    
    If InStr(txt, ob) = 0 Then
        ExtractBracket = ""
        Exit Function
    End If
    
    Dim tl As Long, ol As Long, cl As Long, p1 As Long, p2 As Long, sp As Long
    Dim rp() As String, pc As Long, as_ As Long
    
    tl = Len(txt)
    ol = Len(ob)
    cl = Len(cb)
    sp = 1
    pc = 0
    as_ = 10
    
    ReDim rp(0 To as_ - 1)
    
    Do While sp <= tl
        p1 = InStr(sp, txt, ob)
        If p1 = 0 Then Exit Do
        
        p2 = InStr(p1 + ol, txt, cb)
        If p2 = 0 Then Exit Do
        
        '// 배열 크기 확장
        If pc >= as_ Then
            as_ = as_ * 2
            ReDim Preserve rp(0 To as_ - 1)
        End If
        
        rp(pc) = Mid(txt, p1 + ol, p2 - p1 - ol)
        pc = pc + 1
        sp = p2 + cl
        
        If sp > tl Then Exit Do
    Loop
    
    If pc > 0 Then
        ReDim Preserve rp(0 To pc - 1)
        ExtractBracket = Join(rp, ", ")
    Else
        ExtractBracket = ""
    End If
    
    Exit Function
    
ErrH:
    ExtractBracket = ""
End Function

'// 모든 괄호 통합 추출 함수
Function Extract_All(txt As String) As String
    On Error GoTo ErrH
    
    If Len(txt) = 0 Then
        Extract_All = ""
        Exit Function
    End If
    
    Static ch As Object, lmt As Long
    If ch Is Nothing Then
        Set ch = CreateObject("Scripting.Dictionary")
        lmt = 1000
    End If
    
    If ch.Exists(txt) Then
        Extract_All = ch(txt)
        Exit Function
    End If
    
    Dim r(0 To 2) As String, fp() As String, rc As Long, i As Long, fr As String
    
    r(0) = ExtractBracket(txt, "[", "]")
    r(1) = ExtractBracket(txt, "{", "}")
    r(2) = ExtractBracket(txt, "(", ")")
    
    rc = 0
    ReDim fp(0 To 2)
    
    For i = 0 To 2
        If Len(r(i)) > 0 Then
            fp(rc) = r(i)
            rc = rc + 1
        End If
    Next i
    
    If rc > 0 Then
        ReDim Preserve fp(0 To rc - 1)
        fr = Join(fp, ", ")
    Else
        fr = ""
    End If
    
    If ch.Count < lmt Then
        ch(txt) = fr
    ElseIf ch.Count >= lmt Then
        ClrCh ch, lmt \ 4
        ch(txt) = fr
    End If
    
    Extract_All = fr
    Exit Function
    
ErrH:
    Extract_All = ""
End Function

'// 캐시 부분 정리
Private Sub ClrCh(ByRef ch As Object, cc As Long)
    On Error Resume Next
    
    Dim ks As Variant, i As Long, cd As Long
    ks = ch.keys
    cd = 0
    
    For i = 0 To UBound(ks)
        If cd >= cc Then Exit For
        ch.Remove ks(i)
        cd = cd + 1
    Next i
    
    On Error GoTo 0
End Sub

'// 캐시 초기화
Function ClrCh1() As Boolean
    On Error GoTo ErrH
    
    ClrCh1 = True
    Exit Function
    
ErrH:
    ClrCh1 = False
End Function

Function ClrCh2() As Boolean
    On Error GoTo ErrH
    
    Dim d As String
    d = Extract_All("")
    ClrCh2 = True
    Exit Function
    
ErrH:
    ClrCh2 = False
End Function

=SUBSTITUTE(TRIM(REGEXREPLACE($C$3:$C$102, 
{"\[(.*?)\]|.","\{(.*?)\}|.","\((.*?)\)|."}," $1"))," ",", ")

=LET(Data, SUBSTITUTE(SUBSTITUTE(REGEXREPLACE($C$3:$C$102,
{"\[(.*?)\]|.","\{(.*?)\}|.","\((.*?)\)|."},"($1)"),"()",""),")(",", "),
IFERROR(MID(Data,2,LEN(Data)-2),""))