LinkBack URL
About LinkBacks
I'm trying to clean up a database of links and I need to remove everything after the third instance of a /
I've been googling but haven't found anything like this yet.
Thank you.
I'm trying to clean up a database of links and I need to remove everything after the third instance of a /
I've been googling but haven't found anything like this yet.
Thank you.
Give us a few examples to practice on.
Pete
Something I learnt from one of the gurus here, I think it's called.. padding with spaces and trim? Anyhoo, formulas something like this.
Formula:
=TRIM(LEFT(SUBSTITUTE($A$1,"/",REPT(" ",1000),3),1000))
Last edited by quekbc; 09-02-2015 at 08:04 PM. Reason: Edit following Pete_UK's advice, re-edited after OPs post
I think you need to keep the "/" in there, so replace "/" with "/" plus a load of spaces.
Pete
no, everything AFTER the third...
OK, you need to make up your mind lol...do you want the string BEFORE the 3rd / or AFTER the 3rd /Originally Posted by djbetterly
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
So here is a random web link.
http://www.iconarchive.com/show/soft...xcel-icon.html
But I only want the link to be this, http://www.iconarchive.com, and not all the garbage after it.
Does that help?
The formula given in post#3 with do that.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Try this...
A1 = url
=LEFT(A1,FIND("^",SUBSTITUTE(A1,"/","^",3))-1)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Does that mean you want to extract everything up to the 3rd / ?
One/Two/Three/Four/Five/
Would result in: One/Two/Three/
See my post on #3. That should work.
2 options...
=LEFT(A2,FIND("@",SUBSTITUTE(A2,"/","@",3),1)-1)
=LEFT(A2,FIND(".com",A2,1)+3)
The 2nd 1 may not work properly if there is more than 1 .com
or this
=LEFT(A1,FIND("/",A1,FIND("/",A1,FIND("/",A1)+1)+1)-1)
string from post #3 worked perfect! had to mod it a bit but it worked. Thank you guys. Sorry for any confusion.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks