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

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,155
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
As far as I know it's not possible.
Even using replace in VBA would need to do it 4 times.
 
Upvote 0
something like this?

Column1Column1
https://www.bezaat.com/uae/dubaibezaat.com/uae/dubai
https://www.waseet.ae/waseet.ae/
http://www.eyoon.com/adefeyoon.com/adef
http://www.bazaarye.com/bazaarye.com/
http://souq.ma7room.com/souq.ma7room.com/
http://kulshe.com/-4/kulshe.com/-4/
http://ae.advertrupee.com/ar/ae.advertrupee.com/ar/
https://souqbladek.com/ae/souqbladek.com/ae/
http://dalilak4.com/dalilak4.com/
https://www.albayan.ae/albayan.ae/
http://ae.waseet.net/ar/site/dubai/offersae.waseet.net/ar/site/dubai/offers
http://dubaimoon.com/dubaimoon.com/

Code:
[SIZE=1]let
    Replace = Table.ReplaceValue(Table.TransformColumns(Excel.CurrentWorkbook(){[Name="Table3"]}[Content], {{"Column1", each Text.AfterDelimiter(_, "//"), type text}}),"www.","",Replacer.ReplaceText,{"Column1"})
in
    Replace[/SIZE]
 
Last edited:
Upvote 0
Thanks for your reply Fluff!
Real shame this isn't available in std search/replace :(
Many thanks for letting me know.

And thanks Sandy666!
Yes, thats exactly right.
What I want to do is exactly as you have do it in the tables above,, great stuff!
then I somehow also need to remove everything after the 1st trailing slash.

I came across a website that does this great, took me ages to find this online,,,it's amazing,, just drop in the url list in top frame, select "1st" in lower frame and it does it!
http://www.text-filter.com/tools/remove-everything-after-slash/

Sandy666,
My vba skills are just over non-existant.
The code the you have written,

Are you able to provide this as VBA code, so I can just right click on my sheet, go to view code, and copy/paste a sub in it?

Be great if I could just click a macro button and it removes all these https etc
Also, if at all possible, maybe another click of a button to remove everything after the 1st trailing slash like the website link does above.

That would be awesome if I could do both in 1 spreadsheet.
Not asking for much am I! :)

Only if possible,,
But many thanks for all your replies!

Awesome!
Yours sincerely
John Caines
 
Upvote 0
this is NOT vba but PowerQuery (Get&Transform)

like this?

Column1Column1
https://www.bezaat.com/uae/dubaibezaat.com
https://www.waseet.ae/waseet.ae
http://www.eyoon.com/adefeyoon.com
http://www.bazaarye.com/bazaarye.com
http://souq.ma7room.com/souq.ma7room.com
http://kulshe.com/-4/kulshe.com
http://ae.advertrupee.com/ar/ae.advertrupee.com
https://souqbladek.com/ae/souqbladek.com
http://dalilak4.com/dalilak4.com
https://www.albayan.ae/albayan.ae
http://ae.waseet.net/ar/site/dubai/offersae.waseet.net
http://dubaimoon.com/dubaimoon.com

Code:
[SIZE=1]let
    Replace = Table.TransformColumns(Table.ReplaceValue(Table.TransformColumns(Excel.CurrentWorkbook(){[Name="Table3"]}[Content], {{"Column1", each Text.AfterDelimiter(_, "//"), type text}}),"www.","",Replacer.ReplaceText,{"Column1"}), {{"Column1", each Text.BeforeDelimiter(_, "/"), type text}})
in
    Replace[/SIZE]
 
Last edited:
Upvote 0
With VBA
Code:
Sub JohnCaines()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("https://www.", "http://www.", "https://", "http://")
   For i = 0 To UBound(Ary)
      Range("A:A").Replace Ary(i), "", xlPart, , False, , False, False
   Next i
End Sub
Sub JohnCaines2()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(right(@,1)=""/"",left(@,len(@)-1),@)", "@", .Address))
   End With
End Sub
 
Upvote 0
why you want vba if you've Get&Transform built-in ???
 
Last edited:
Upvote 0
Many thanks for your replies!!

1st off Sandy666....
Thing is Sandy, I think sometimes you guys under estimate how clever you are here or what you know.
You kind of almost take it for granted saying "why you want vba if you've Get&Transform built-in ??? "

What I mean by this is Sandy666, I've even had to google what powerquery is!
Now you say, get-transform/ I've never used it, never heard of it to be honest! :(
I kind of get by in excel, I really do need to learn more, especially I want to watch some tutorials on pivot tables, as I haven't even got to those yet!

The reason VBA, for me is a simple 1.
I click 1 button, job done!
I can save a spreadsheet, call it whatever, and when I need to say strip https, I can just open up this saved spreadsheet, insert data into column A and press 1 button and jobs a gooden.

I will look into this power quiery Sandy666, but again, you are a clever guy in excel, for you,,,, power query/get transform might seem obvious..
for me, it's another can of worms! Another big learning curve, it's all time, and time is a killer for all of us to some degree
So for me Sandy666, If I can just press a button and get the result, then presto, job done! :)

I will watch some videos over weekend on it for sure. Many thanks again Sandy666

=========================
As to Fluff,,

Many thanks for this!
Yes, this is brilliant.
Really neat. Excellent!!,,,,,,, arrrr, but,,,,
All that said,,,,can the John2 sub be adjusted Fluff?
I've just tried to run it and it returned the following.
=========
Data before I ran both subs...................

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/
==================
Data after I ran both subs...................

bezaat.com/uae/dubai
waseet.ae/
eyoon.com/adef
bazaarye.com/
souq.ma7room.com/
kulshe.com/-4/
ae.advertrupee.com/ar/
souqbladek.com/ae/
dalilak4.com/
albayan.ae/
ae.waseet.net/ar/site/dubai/offers
dubaimoon.com/
==================
Data required..................

bezaat.com
waseet.ae
eyoon.com
bazaarye.com
souq.ma7room.com
kulshe.com
ae.advertrupee.com
souqbladek.com
dalilak4.com
albayan.ae
ae.waseet.net
dubaimoon.com
en.sptechs.com
====================
If possible Fluff can it strip all data after the 1st trailing slash please from the left? That what I was really after....
Code now is;
Code:
Sub JohnCaines2()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(right(@,1)=""/"",left(@,len(@)-1),@)", "@", .Address))
   End With
End Sub
I tried to believe for 1 minute I was a VBA coder Fluff as I saw the word 'Right' and the word 'left' and thought maybe the code is looking at the slashes the wrong way around, so I changed the words around,,, thinking it might work! You know the answer already!!
:( worth a try!!

If you can fix this Fluff so it returns the data like in "data required" list,,,, it would be amazing Fluff....at least I tried!

Many thanks to you both again.
I really appreciate your help here,, and again, sorry Sandy666, but I will read up on querys,, but again,, I'm a Leonard "Kelly" Johnson fan.... K.I.S.S. !! :)
Best regards
and thanks again
John Caines
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top