Extract external workbook full path from formula

Hi Excel VBA experts,

I have the following formula which is a text.

Please Login or Register  to view this content.

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.

Hi,

use the VBA Split function with the single quote as delimiter then you can check each result if containing any square bracket …

As above Marc L, but first use 'Replace' function for "'" and "!" with replacement e.g. "|", and then Split, and then a loop with 'IF' statement for Left(string, 2) = "\\", and then a 'Dictionary'.

I forgot : using Split function a Dictionary is very not necessary …