I was hoping someone had already written it.
The behavior you describe sounds a lot like the behavior of Google Sheets built in Split function https://support.google.com/docs/answer/3094136?hl=en If I put that text into a Google Sheets cell and enter =SPLIT(A1,"[]") into an adjacent range, it outputs an array just like you describe. VBA's Split() function behaves differently, and Excel does not even have a built in Split function, so this really only works in Google Sheets. If you are not absolutely required to do this in Excel/VBA, and Google sheets is a viable option, then this might be the easiest approach.
I won't speak for others, but I think if I were trying to do this in VBA, I would change the input string into something that would "fit" into the behavior of VBA's Split function. https://docs.microsoft.com/en-us/off...split-function
If I had control over the input string, rather than using open [ and close ] brackets, I would use one or the other.
If I had to allow the input to have both characters, I would use a Replace function (https://docs.microsoft.com/en-us/off...place-function ) to change the input string to something more suitable for the Split() function:
Putting something like this into a UDF should be straightforward.
Some variation on one of those approaches is probably how I'd do it.
Bookmarks