I'm trying to solve a problem at work that is perplexing me.
I have a data set which is exceeding 500,000 cells which is the limit Google Sheets has for a document. At work we by and large use Google Sheets so we don't switch to Excel unless it exceeds a limitation of Google Sheets.
I have a very large set of URLs and they need to have the text after the 4th / (forward slash) character in certain instances.
In Google Sheets I have the function working as this...
=IF(NOT(REGEXMATCH(C2,"moreinfo")),"Category or Filter",INDEX(SPLIT(C2,"/"),0,4))
Which works exactly as desired. In URLs that contain the "moreinfo" text you get the text after the 4th forward slash because Google Sheets has the INDEX and SPLIT functions which work for fairly elegant solutions like this.
INDEX accepts an array, and SPLIT splits a set of text into an array.
I was able to work out an equivalent to REGEXMATCH() which works as expected by using ISNUMBER(SEARCH("moreinfo",C2)).
However I can't find anything good or elegant as alternatives for the INDEX() and SPLIT() functions. Is there any such animal in Excel? Everything I've been finding involves extremely long hard to follow functions using RIGHT(), LEFT(), LEN(), MID(), and SEARCH().
