Can I multiple find and replace in 1 go in excel 2016?

[John Caines]

Hello All
I'm wondering is there a way to delete multiple searches in 1 go with find and replace?

Here's my example;
Say I have a list of URLs in Column A
List below (maybe 1000 URLs)
===========
https://www.bezaat.com/uae/dubai
https://www.waseet.ae/
http://www.eyoon.com/adef
http://www.bazaarye.com/
http://souq.ma7room.com/
http://kulshe.com/-4/
http://ae.advertrupee.com/ar/
https://souqbladek.com/ae/
http://dalilak4.com/
https://www.albayan.ae/
http://ae.waseet.net/ar/site/dubai/offers
http://dubaimoon.com/
==============
I want to delete all the http's like;

Some are https://www.
Some are http://www.
Some are https://
Some are http://

==============
Is it possible to use find and replace and somehow delete all 4 of these in 1 go if I select Column A?
Otherwise I have to do this 4 separate times, be great if I could do this in 1 go.

Does anyone know if this is possible in excel 2016 please, and if so, how!

I'm sure a VBA could do it (and would love a vba sheet if possible),, that said, I really wanted to know if I could just do multiple find/replace in 1 go
Here's an image just to illustrate

https://imgur.com/a/5mJRqEG

So somehow have the 4 queries all replaced with nothing in 1 go.

Hope this makes sense.
Be great if I could somehow manage this in 1 go

Yours sincerely
A very perplexed..
John Caines

 

[John Caines]

Thanks Sandy666

Yes, I did see post #5 , I actually thought that code was some vba code! hahaha,,, ,,, completely blind to get/transform...

I'm watching a video on youtube right now about power query, as I don't know nothing about it, (I dont even see it in my ribbon), so I guess I have to activate it,,,,

I'll watch the video, should be a lot clearer to me after that.
https://www.youtube.com/watch?v=BDHX8GT6UpQ
How to Use Power Query / Get And Transform in Microsoft Excel 2016

Hopefully this will get me enlightened!

Many thanks again Sandy666,
I always appreciate everyones help here,,

Yours sincerely
John Caines

 

[Fluff]

How about

Sub JohnCaines2()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(isnumber(find(""/"",@)),left(@,find(""/"",@)-1),@)", "@", .Address))
   End With
End Sub

 

[John Caines]

I know I don't know you Fluff,,,
but rumour has it you are a legend!!! ,,,, and it's true!!

That's brilliant,,,
You don't realise how much time this will save me.

Really handy for me going forward ...
Many thanks again for this.

I'm getting off the PC now for a while with a big smile on my face,,, no more clicking in individual cells and using the backspace key!!

Thanks Fluff!
A very grateful
John Caines

 

[jtakw]

Hi,

In case you want to consider a formula solution, formula copied down:

Book1
	A	B
1	https://www.bezaat.com/uae/dubai	bezaat.com
2	https://www.waseet.ae/	waseet.ae
3	http://www.eyoon.com/adef	eyoon.com
4	http://www.bazaarye.com/	bazaarye.com
5	http://souq.ma7room.com/	souq.ma7room.com
6	http://kulshe.com/-4/	kulshe.com
7	http://ae.advertrupee.com/ar/	ae.advertrupee.com
8	https://souqbladek.com/ae/	souqbladek.com
9	http://dalilak4.com/	dalilak4.com
10	https://www.albayan.ae/	albayan.ae
11	http://ae.waseet.net/ar/site/dubai/offers	ae.waseet.net
12	http://dubaimoon.com/	dubaimoon.com
Sheet642

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(MID(SUBSTITUTE(A1,"/",REPT(" ",99),3),FIND("//",A1)+2,99),"www.",""))

 

[Fluff]

Glad to help & thanks for the feedback

 

[John Caines]

Glad you helped me Fluff! Cheers again

Just seen your reply jtakw.
Very nice to!!

I have just added it to my new 'http stripper' sheet!!
Just 1 question,, I see the number 99 in the formula.....

Is that a character length limitation of the URL?

Sometimes I do have long URLs,, just wondered that was all.

Many thanks again,,
Really appreciated.

Best regards to you all
A very happy
John Caines

 

[jtakw]

You're welcome.

Your URLs are Not limited by the 99 within my formulas, it does, however, limits the length of the Domain Name itself, as in the results in Column B in my sample.
If you think you may have Domain Names longer than that, just replace All instances of 99 to 200, 255, 300 or higher as you wish.

 

[John Caines]

Great stuff!
Many thanks again jtakw,,

Thanks Fluff, and Sandy666

Post closed!!
Brilliant!

Yours sincerely
A very grateful
John Caines

Thank you all!!