Find and Replace Macro or suggestions on other ways to approach?

[DDarling]

I have a list of thousands of URL's that I need to regularly edit and I have been manually doing this with a find and replace, but I would like to automate the work. Unfortunately there is very little consistency in the domain structure and a text to columns approach won't work.

I need to do the following with this list of URL's:
-Eliminate the "WWW" in front of all domains when it appears (this is the easiest find and replace aspect)
-Revise all sub-domains to only show the domain level info (remove the sub-domain part of the domain). For example, "help.cnn.com" needs to change to "cnn.com". However, there are thousands of sub-domain variations and keeping a list of them all is not an ideal way to approach (but keeping a list is possible if there are no other options)
-For country specific domains I need to keep the country parts of the URL.

For example, I need the list to go from:

Breaking News, U.S., World, Weather, Entertainment & Video News - CNN.com
help.cnn.com
blog.news.com
bbc.co.uk

To this:
cnn.com
cnn.com
news.com
bbc.co.uk

My domain list is in column E.

Is there a macro that can be created to do this or does anyone have alternative recommendations? when doing find and replace via a macro and a list can I still use the "*" function to find and replace any sub-domain against a specific domain (i.e. can I search *.cnn.com to change to cnn.com via a macro)

Thanks!

 

[István Hirsch]

This formula turns your source strings into the requested ones. Try it with an extended list of URLs:

=LOWER(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",REPT(" ",20)),(1*ISNUMBER(SEARCH(".??#",A1&"#"))+2)*20))," ","."))

 

[DDarling]

This formula turns your source strings into the requested ones. Try it with an extended list of URLs:

=LOWER(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",REPT(" ",20)),(1*ISNUMBER(SEARCH(".??#",A1&"#"))+2)*20))," ","."))

This formula turns your source strings into the requested ones. Try it with an extended list of URLs:

=LOWER(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",REPT(" ",20)),(1*ISNUMBER(SEARCH(".??#",A1&"#"))+2)*20))," ","."))

Ivan,

Thank you very much for the help. This worked for most of the domain strings and even if this is the best we can come up with it will save me a significant amount of time.

It did not work perfectly for domains that end with a two digit code in the following ways:

-The "www." aspect is not removed for domains ending in two digits. Examples below were unchanged by the formula:[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]artnails.bestpicts.me
[/TD]
[/TR]
[TR]
[TD="class: td1"]artnat.grejt.pl
[/TD]
[/TR]
</tbody>[/TABLE]

The last example of something I was that didn't seem to work perfectly is that the string "007.microsoftadvertisingexchange.com" was changed to "vertisingexchange.com". I can't understand this particular issue well as it has nothing to do with the other issues and is the only ".com" domain that I saw impacted. I will admit that I'm unable to go through each and every line to see if there are other instances of similar challenges, but I did a review and didn't find others.

Thank you very much for your help.


<table cellspacing="0" cellpadding="0" class="t1" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"=""><tbody>[TR]
[TD="class: td1"]


[/TD]
[/TR]
</tbody></table>

 

[DDarling]

This formula turns your source strings into the requested ones. Try it with an extended list of URLs:

=LOWER(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",REPT(" ",20)),(1*ISNUMBER(SEARCH(".??#",A1&"#"))+2)*20))," ","."))

Ivan,

Thank you very much for the help. This worked for most of the domain strings and even if this is the best we can come up with it will save me a significant amount of time.

It did not work very well for domains that end with a two digit code in the following ways (I hope this make sense!):

-The "www." aspect is not adjusted correctly for domains ending in two digits. Sometimes no changes are made, sometimes the formula removes one or two of the "W's".

Example: "www.teamspeak-verleih.de" became "w.teamspeak-verleih.de". The formula removed two of the "W's" in the "WWW", but not all of them. This happened multiple times and sometimes removed one or two of the W's.

-I think the issue impacts not just the "WWW" of course, but all sub-domains for domains that end with a two digit code. Overall the formula seemed to do a number of different things two domains ending in 2 digits.

I also think I might have an issue that prevents a perfect formula being easily implemented. The issue is shown through the examples below:

Example 1: "zshanakaz.bestpicts.me" and "zpravy.aktualne.cz" were left unchanged by the formula. I would love to be able to remove the subdomains here as well and show just "bestpicts.me" and "aktualne.cz"

However looking at example 2 below and comparing it to example 1 above I discovered a problem.

Example 2: ºÎ³¬ - ´ëÇѹα¹ ¹Ù´Ù³¬½ÃÀÇ ¸ÞÄ« was correctly changed to "ybada.co.kr".

In the case of Example 2 I want to keep the sub-domain, because it's not really a sub-domain, you actually need the entire string as the website isn't "co.kr". However, in Example 1 the sub-domains should be removed. Would I be correct to say that this complicates the use of a formula as a solution unless we're building a ton of if/then statements?

The last example of something I was that didn't seem to work perfectly is that the string "007.microsoftadvertisingexchange.com" was changed to "vertisingexchange.com". I can't understand this particular issue well as it has nothing to do with the other issues and is the only ".com" domain that I saw impacted. I will admit that I'm unable to go through each and every line to see if there are other instances of similar challenges, but I did a review and didn't find others.

Thank you very much for your help.

 

[István Hirsch]

Unfortunately, I do not exactly know the rules of constructing URLs so please list the URLs where the formula fails along with the required result.

Please change 20 in the formula to 100 (at both locations) to avoid problems with long strings („007….”)

 

[DDarling]

Ivan,

When I do a find and replace for "WWW." and remove the "WWW." from the domains before I apply the formula it works better.

Ideally, length of the domain wouldn't have any impact, however I see how to adjust the numbers in the formula per your direction.

Below are two tables that listed the original and desired URL's. The first table is specific to instances where the desired outcome did not match the result of the formula. The second table has just some more basic examples of the original and desired outcomes.

Thank you again for your help. The current status is far improved over my initial state. If we can further improve the outcome great, if not no worries and thanks.


[TABLE="width: 900"]
<tbody>[TR]
[TD]Original:[/TD]
[TD]Revised After Formula Applied:[/TD]
[TD]Desired Outcome:[/TD]
[/TR]
[TR]
[TD]forum.4pforen.4players.de[/TD]
[TD]4pforen.4players.de[/TD]
[TD]4players.de[/TD]
[/TR]
[TR]
[TD]beruskavn.rajce.idnes.cz[/TD]
[TD]rajce.idnes.cz[/TD]
[TD]idnes.cz[/TD]
[/TR]
[TR]
[TD]fiks-s-c-biuro-ksiegowe-i-laczynska-l.znanyprawnik.pl[/TD]
[TD]aczynska-l.znanyprawnik.pl[/TD]
[TD]znanyprawnik.pl[/TD]
[/TR]
[TR]
[TD]chicagoarchitecture.info[/TD]
[TD]hicagoarchitecture.info[/TD]
[TD]chicagoarchitecture.info[/TD]
[/TR]
[TR]
[TD]ultimate-newone.blogspot.fi[/TD]
[TD]ltimate-newone.blogspot.fi[/TD]
[TD]blogspot.fi[/TD]
[/TR]
[TR]
[TD]davidhills.dailyfunnypics.me[/TD]
[TD]vidhills.dailyfunnypics.me[/TD]
[TD]dailyfunnypics.me[/TD]
[/TR]
[TR]
[TD]motors.friday-ad.co.uk[/TD]
[TD]friday-ad.co.uk[/TD]
[TD]friday-ad.co.uk[/TD]
[/TR]
[TR]
[TD]inframe.platform.onet.pl[/TD]
[TD]platform.onet.pl[/TD]
[TD]onet.pl[/TD]
[/TR]
[TR]
[TD]forumarchiwum.gry-online.pl[/TD]
[TD]orumarchiwum.gry-online.pl[/TD]
[TD]gry-online.pl[/TD]
[/TR]
[TR]
[TD]heavyequipmentforums.com[/TD]
[TD]eavyequipmentforums.com[/TD]
[TD]heavyequipmentforums.com[/TD]
[/TR]
[TR]
[TD]springfieldspringfield.co.uk[/TD]
[TD]ringfieldspringfield.co.uk[/TD]
[TD]springfieldspringfield.co.uk[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 466"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]Original:[/TD]
[TD]Desired:[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]www.cnn.com[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]cnn.com[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]www.sports.cnn.com[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]cnn.com[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]sports.cnn.com[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]cnn.com[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]bbc.co.uk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]bbc.co.uk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]www.bbc.co.uk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]bbc.co.uk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]07da8c2485ccf10a.anonymous.google[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]anonymous.google[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]sports.khan.co.kr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]khan.co.kr[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]hotgirlscollection.hostedgalleries.me[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]hostedgalleries.me[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]www.store.na.square.com[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]square.com[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]forumarchiwum.gry-online.pl[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]gry-online.pl[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 209"]
<tbody>[TR]
[TD]m.birdtrader.co.uk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD]birdtrader.co.uk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[István Hirsch]

Enter into B1 and copy down:

=LOWER(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",REPT(" ",100)),(1*ISNUMBER(SEARCH(".co.??#",A1&"#"))+2)*100))," ","."))

In column C the desired results are listed, in col D „OK” means that the result given by the formula and the requested result are the same.

Excel Workbook
	A	B	C	D
1	Breaking News, U.S., World, Weather, Entertainment & Video News - CNN.com	cnn.com	cnn.com	OK
2	help.cnn.com	cnn.com	cnn.com	OK
3	blog.news.com	news.com	news.com	OK
4	bbc.co.uk	bbc.co.uk	bbc.co.uk	OK
5	forum.4pforen.4players.de	4players.de	4players.de	OK
6	beruskavn.rajce.idnes.cz	idnes.cz	idnes.cz	OK
7	fiks-s-c-biuro-ksiegowe-i-laczynska-l.znanyprawnik.pl	znanyprawnik.pl	znanyprawnik.pl	OK
8	chicagoarchitecture.info	chicagoarchitecture.info	chicagoarchitecture.info	OK
9	ultimate-newone.blogspot.fi	blogspot.fi	blogspot.fi	OK
10	davidhills.dailyfunnypics.me	dailyfunnypics.me	dailyfunnypics.me	OK
11	motors.friday-ad.co.uk	friday-ad.co.uk	friday-ad.co.uk	OK
12	inframe.platform.onet.pl	onet.pl	onet.pl	OK
13	forumarchiwum.gry-online.pl	gry-online.pl	gry-online.pl	OK
14	heavyequipmentforums.com	heavyequipmentforums.com	heavyequipmentforums.com	OK
15	springfieldspringfield.co.uk	springfieldspringfield.co.uk	springfieldspringfield.co.uk	OK
16	www.cnn.com	cnn.com	cnn.com	OK
17	www.sports.cnn.com	cnn.com	cnn.com	OK
18	sports.cnn.com	cnn.com	cnn.com	OK
19	www.bbc.co.uk	bbc.co.uk	bbc.co.uk	OK
20	07da8c2485ccf10a.anonymous.google	anonymous.google	anonymous.google	OK
21	sports.khan.co.kr	khan.co.kr	khan.co.kr	OK
22	hotgirlscollection.hostedgalleries.me	hostedgalleries.me	hostedgalleries.me	OK
23	www.store.na.square.com	square.com	square.com	OK
24	forumarchiwum.gry-online.pl	gry-online.pl	gry-online.pl	OK
25	m.birdtrader.co.uk	birdtrader.co.uk	birdtrader.co.uk	OK
Sheet

 

[DDarling]

Ivan,

This seems to work very well. Thank you for your assistance I appreciate it.

Thank you,
Daniel

 

[István Hirsch]

You are welcome, thanks for the feedback.