Extracting URLs domain name from various URL domain combos

[thp510]

I have various lists of website URLs in all shapes and sizes and I want to just have each website to be in this format: www.[insert your domain name].com Examples:

Column A
www.normals.com (pretty straight and fwd. About 80% of my list is like this and I don't need to change this)
https://www.securityreview.com (there's an http in front of the www and also there's an S in http)
http://raodstop.com/ (there's an http in front and there's no www)
www.raodstop.com/p/gda/31/ext (there's a lot of text in this URL after the .com portion)
truradio.de (there's no http or www for this URL. Plus I'd like to keep the full domain on this so it's www.truradio.de and NOT www.trueradio.de.com)


Does anyone have a formula that I can use to trim down column A and add www. AND also add .com to all of them?

 

[AlanY]

this works for your sample data

=IFERROR(LEFT(A1,FIND(".com",A1)+3),"www."&A1)

 

[FormR]

Hi, here's another take..:

Excel 2013/2016
	A	B
1	www.normals.com	www.normals.com
2	https://www.securityreview.com	www.securityreview.com
3	http://raodstop.com/	www.raodstop.com
4	www.raodstop.com/p/gda/31/ext	www.raodstop.com
5	truradio.de	www.truradio.de
Sheet1

Cell Formulas
Range	Formula
B1	="www."&TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"www.","",1),"http://","",1),"https://","",1),"/",REPT(" ",255)),255))

 

[thp510]

This is amazing! Thank you so much. It worked!!!!

Hi, here's another take..:

Excel 2013/2016

	A	B
www.normals.com
https://www.securityreview.com	www.securityreview.com
http://raodstop.com/	www.raodstop.com
www.raodstop.com/p/gda/31/ext	www.raodstop.com
truradio.de	www.truradio.de

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFFF00"]www.normals.com[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]="www."&TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"www.","",1),"http://","",1),"https://","",1),"/",REPT(" ",255)),255))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]