0

I previously asked for help with a formula here

I was able to put together a formula but now need to add some additional functions to it & I am lost now.

My current formula is this:

=proper(replace(regexreplace(regexreplace(INDEX(F:F,ROW()),"https://",""),"www.",""),find(".",regexreplace(regexreplace(INDEX(F:F,ROW()),"https://",""),"www.","")),500,""))

This removes:

https:// & www. & all text after the last period

in any website entered into Column F and replaces it with nothing, which allows us to extract the website name only.

Now, we are needing the ability to remove:

https://store. & https://ca.

In our formula as well and for the life of me, I cannot figure out how to get this into my formula.

Could anyone here be of assistance?

1
  • Have you then tried the second formula that I proposed to you in your previous question?
    – Daniele
    Aug 1 at 15:00

1 Answer 1

0

Assuming that the URLs you want to extract are in column F2:F, put this formula in row 2 of a free column:

=arrayformula( iferror( regexextract(F2:F, "(?:https?://)?(?:(?:www|store|ca)\.)*([-_\w]+)") ) )

To learn the exact regular expression syntax used by Google Sheets, see RE2.

6
  • I believe that OP should provide us with some example of url with relative expected result. Your formula, like mine, gives different results even for the same url ... try www.ca.arubapec.it/ and https://ca.arubapec.it/
    – Daniele
    Aug 1 at 15:31
  • Agreed. Edited the answer to allow repeats in www|store|ca. Aug 1 at 15:39
  • Thank you! That formula seems to do exactly what I need it to do except: In my previous formula, I had the =INDEX(F:F,ROW()). The reason for this, is a chrome extension we use exports set data automatically for us to Google sheets. One of our custom fields is this formula. As far as I know, the extension needs this in order to apply the formula correctly. I attempted to apply that to your new formula without luck. Any ideas?
    – Jordan
    Aug 2 at 9:04
  • The array formula will process all of column F2:F in one go. It should work fine provided that your extension appends rows at the bottom of the sheet rather than at the top. Please ask only one question per post. Aug 2 at 9:14
  • I was able to figure it out, thank you!
    – Jordan
    Aug 2 at 9:14

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.