0

How can I extract the website title name only from one cell and auto paste it in another cell in Google Sheets?

I have a spreadsheet where we are constantly adding URLs from multiple different websites & we need to then manually input what website its for.

For example:

google.com/jhsdgfjkhsgd in A1, I need to manually input Google in B1 wikipedia.com/therock in A2, I need to manually input Wikipedia in A2

Is there a formula I can paste into the entire A column or script I can use to do this automatically?

I saw a previous post on here (Extract the link text and URL from a hyperlinked cell) but those options did not work for me.

1
  • "That works beautifully! Thank you!" Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Jul 22 at 8:53

2 Answers 2

1

Try with this formula, placed in B2:

=index(regexreplace(A1:A,"\..+",""))
  1. the regular_expression \..+ selects all text from dot to end
  2. the regexreplace() function replaces that selection with empty
  3. the index() function applies that formula to all the links of column A

if all your urls have www. in front, use this formula instead:

=index(if(A1:A<>"",regexextract(A1:A,"\.(.+)\."),))

this always extracts the text contained between two points

5
  • That works beautifully! Thank you! Although many times (actually every time) our URLS will have the whole (https - it won't let me display the https in the whole link) www. in front of it. Is there a way to adjust the formula to account for that?
    – Jordan
    Jul 21 at 22:43
  • I just found this: =REGEXREPLACE(A1,"","") Which can remove the unwanted text in front but I cannot figure out a way to combine the two forumlas without having to use multiple cells
    – Jordan
    Jul 21 at 22:55
  • if all your urls have www. in front, use this formula: =index(if(A1:A<>"",regexextract(A1:A,"\.(.+)\."),)) this always extracts the text contained between two points
    – Daniele
    Jul 22 at 0:13
  • Great. Remember though, you can also edit your answer to include your alternatives. Not all people make it down to comments. Jul 22 at 8:55
  • 1
    @marikamitsos done :)
    – Daniele
    Jul 22 at 9:19
0
=proper(replace(regexreplace(regexreplace(INDEX(F:F,ROW()),"https://",""),"www.",""),find(".",regexreplace(regexreplace(INDEX(F:F,ROW()),"https://",""),"www.","")),100,""))

This is what I ended up using as the URLs we use are from all different sites & some include the https with no www and some include both.

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.