Hi Excel VBA experts,

I have the following formula which is a text.

=IF(…,SUMIFS('Source'!$H:$H, ….., 'Source'!$Z:$Z, T$3),
      IF(…,SUM(IF('\\some network drive name\….\….\[Workbook1 Name.xlsx]Sheet1'!$A$1:$A$100=$F50, '\\some network drive name\….\….\[Workbook1 Name.xlsx]Sheet1'!$B$1:$B$100)),
      IF(…,SUM(IF('\\some network drive name\….\….\[Workbook2 Name.xlsx]Sheet9'!$A$1:$A$100=$F50, '\\some network drive name\….\….\[Workbook2 Name.xlsx]Sheet9'!$B$1:$B$100)),
      '\\some network drive name\….\….\[Workbook3 Name.xlsx]Sheet5'!G1)))
How do I go about looking at this formula text from the end and search for a right square bracket ']' and if I do find it, continue searching towards the start of the string and find a single quote (') and then put it in a collection/dictionary which later than retrieve it to excel range.

As you can see, in the above formula (which is made up and not tested), there's 3 external workbooks, that's colored in red, blue and orange. The one that color in lime is not an external workbook, but a sheet name within it's own workbook, and I'm not interested in it. I'm only interested in a string that starts with a single quote and have square brackets ([…]) as the sheet name.