In Google Docs spreadsheet how do I index into array returned by REGEXEXTRACT

Question

I am trying to index into the results of REGEXEXTRACT to pull out specific portions of a text string that is a path. How do I access specific matches.

  A1                                          A2
  ------------------------------------        -----------
  =REGEXEXTRACT($B$2,"/[A-Za-z_-]+/")         /a/b/c/d.txt

The above formula puts the first array value "/a/" into cell A1, how would I access the 4th or last value in the array?

Answer 1

If you insist on using REGEXTRACT() to break your path down, you may find the following resources to be helpful:

• Regex for extracting filename from path
• How to retrieve all parts of path-like string using regular expressions? (which concluded "don't - use javascript instead"... so I started by lifting code from an example there!)
• Regular expression to extract part of a file path using the logstash grok filter
• ... and many many more.

However, in a Google Spreadsheet, the most effective way to access specific portions of a path & filename string is to use a custom function in an embedded script. I'll assume you're familiar with apps-script programming; see Building Your First Script.

The following code contains two scripts, one built on the other. For example, calling pathExtract() will return a specified segment of a given path string.

/**
 * Split path into parts, return in array. If path started
 * at 'root', first part will be "/". This function may be used
 * as a custom function in Google Spreadsheets.
 *
 * @var {string} path   The search path, e.g. "/a/b/c/d.txt"
 * @return {array}      Result of splitting path at "/". 
 */
function pathSplit( path ) {
  var arr = path.split('/');
  if (path[0] === "/") {
    arr[0] = "/";
  }
  return ( arr );
}

/*
 * Get a portion of a path string. To match INDEX() function,
 * the parameter index is 1-based. Requesting index 0 will
 * result in the last element from the string. This function
 * may be used as a custom function in Google Spreadsheets.
 *
 * @var {string} path   The search path, e.g. "/a/b/c/d.txt".
 * @var {number} index  1-n, the element to extract. 0 for last.
 * @return {string}     Element [index] from path, or *ERROR*.
 */
function pathExtract( path, index ) {
  var result = "*ERROR*";
  var pathArray = pathSplit( path );
  if (index >= 1 && index <= pathArray.length) {
    result = pathArray[index-1];
  }
  if (index === 0) {
    result = pathArray[pathArray.length-1];
  }

  return ( result );
}

Here's a few examples of what you can do with those scripts added to your spreadsheet: