2

How can I get a subrange of second to last element in a formula under Google Sheets?

I'm trying to extract all occurrences of https://example.com/* in a cell which may contain multiple URLs scattered across a string like:

abc https://example.com/abc https://google.com/ghi https://example.com/def/abc

so that I get the paths in a single string, separated by spaces as follows:

abc def/abc

As SPLIT looks the easiest way for that, I need to get rid of the first item in a formula like:

=join("
",
  arrayformula(
    regexextract(
      split(A5, "https://example.com/", false, true),
      "[a-zA-Z0-9/\?=_]+")
    )
  )
  • Please provide an example of the contents of the cell. – Tedinoz Oct 15 at 3:10
  • I made some guesses of what OP intends to use as input and output. Please correct if wrong but this makes it more useful for others and allows a concrete solution. – Cornelius Roemer Oct 15 at 20:12
  • @CorneliusRoemer Your sample data is just right for the problem at hand. Thanks for the well-designed solution and a step-by-step explanation for it! – wass rubleff Oct 15 at 20:31
  • You are welcome :) please accept the solution too, to show it solved the problem! – Cornelius Roemer Oct 16 at 8:32
2

You have the basic concept right just piece it together step by step. This does the job:

=REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR(
  REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE),
  "https://example.com/(\S+)"),""))),"\s+"," ")
  1. You split the string at whitespaces, this is safe because URLs don't contain white space so you're definitely not breaking them up. This gives you an array of strings
  2. Since you want all the paths for all the domains starting with example.com you REGEXEXTRACT exactly those paths and only those paths using a capturing group (\S+).
  3. Now you will get an error for every string that doesn't have the correct domain. So you use IFERROR to simply replace those strings with empty strings, we don't care about those.
  4. We wrap all of this in ARRAYFORMULA since the output of split is an array. So now we have an array of empty strings and paths.
  5. We now JOIN them together using white space, again, paths don't contain white space so we're not losing any structure.
  6. Lastly, we're cleaning up any white space tuples since they're unnecessary and not pretty using REGEXREPLACE. Done!

The result is a string of all the paths for the correct domain only, separated by one whitespace each.

Tip: I built this formula step by step, not as one big formula but piecewise in exactly the sequence outlined above to keep things manageable.

enter image description here

  • 1
    One thing I missed: looks like your approach results in a leading space whenever the cell does not starts with the URL. I discovered that when I used line break instead of space. How can I get rid of leading separator? – wass rubleff Oct 20 at 23:21
  • You're right about the leading space - if you mind it you can replace it with another REGEXREPLACE(string, "^\s","") where the command above is to be put in place of string. – Cornelius Roemer Oct 21 at 9:02
  • If possible, I wish we could have a cleaner way, removing an array item right after initial REGEXTRACT(SPLIT) functions, which brings us back to the original "second to last element subrange" question we originally started with :-) – wass rubleff Oct 22 at 19:29
  • @wassrubleff I'm not sure I understand what you mean by removing array item, can you explain? – Cornelius Roemer Oct 23 at 10:41
  • I meant the following. The leading separator in your answer is there for strings starting with non-URL because the SPLIT > REGEXTRACT results in an empty first item, and then it is JOINed together using a space which results in a leading separator in the resulting string. But now I also realize that any multiple non-URL words occurred between any two URLs also result in repeated delimiter which still should be addressed--again it would be cleaner to remove it before JOINing together, but I'm not big expert on how we can filter the pre-JOIN array to contain only non-empty items. – wass rubleff Oct 31 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.