Hoe cell-referenties uit formules in Excel te extraheren?

Stel dat je enkele lange formules hebt, en nu wil je alle celverwijzingen uit de formules extraheren zoals in de onderstaande schermafbeelding wordt getoond. Hoe kun je de celverwijzingen zo snel mogelijk uit de formules halen?

Celverwijzingen uit formules extraheren met een door de gebruiker gedefinieerde functie

Celverwijzingen uit formules extraheren met een door de gebruiker gedefinieerde functie

Om de celverwijzingen uit formules te extraheren, kan de volgende VBA-code je helpen. Volg de onderstaande stappen:

1. Houd de toetsen ALT + F11 ingedrukt om het venster Microsoft Visual Basic for Applications te openen.

2. Klik op Invoegen > Module, en plak de volgende code in het modulevenster.

VBA-code: Celverwijzingen uit formules extraheren:

Function ExtractCellRefs(Rg As Range) As String
'Updateby Extendoffice
    Dim xRetList As Object
    Dim xRegEx As Object
    Dim I As Long
    Dim xRet As String
    Application.Volatile
    Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
    With xRegEx
        .Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?"
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With
    Set xRetList = xRegEx.Execute(Rg.Formula)
    If xRetList.Count > 0 Then
        For I = 0 To xRetList.Count - 1
            xRet = xRet & xRetList.Item(I) & ", "
        Next
        ExtractCellRefs = Left(xRet, Len(xRet) - 2)
    Else
        ExtractCellRefs = "No Matches"
    End If
End Function

3. Nadat je de code hebt geplakt, sla je de code op en ga je terug naar het werkblad, en voer je deze formule in: =extractcellrefs(G2) (G2 is de cel die de formule bevat waaruit je de celverwijzingen wilt extraheren) in een cel waar je het resultaat wilt krijgen, en druk vervolgens op Enter. Alle celverwijzingen zijn nu geëxtraheerd uit de formule, zie onderstaande schermafbeelding: