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. Commented Jul 22, 2022 at 8:53

3 Answers 3

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
    Commented Jul 21, 2022 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
    Commented Jul 21, 2022 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
    Commented Jul 22, 2022 at 0:13
  • Great. Remember though, you can also edit your answer to include your alternatives. Not all people make it down to comments. Commented Jul 22, 2022 at 8:55
  • 1
    @marikamitsos done :)
    – Daniele
    Commented Jul 22, 2022 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.

-1

I got a pretty indirect method to extract all url. Export the column of embedded url titles in html file. Then online URL extractor would do the job.

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Apr 18, 2023 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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