Dear Excel Experts,
I have 5000 urls in one column and I want to take some part of the content from the urls and wanted put in next column in their respective cell.
Please find the sample to see how I wanted the data in the next column.
Please help.
Thanks in advance.
in c1 copy paste below then drag down
=SUBSTITUTE(MID(A1,SEARCH("sub_",A1)+4,(FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))-(SEARCH("sub_",A1)+4)),"_0_"," ")
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Thanks for your suggestion but it is not working for all. When I applied this formula to Sample 2, it is not working. Please find the attachment and look for Sample 2 sheet. I hope you will understand.Originally Posted by hemesh
Waiting for your reply.
Regards
for this you need to use below in C1
=SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("@",SUBSTITUTE(A1,"/","@",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,100),".aspx",""),"_"," ")
If both types of the sample are mixed you could use this:Formula:
Please Login or Register to view this content.
Check if this one works..Formula:Please Login or Register to view this content.
Last edited by Saarang84; 05-16-2014 at 05:25 AM.
If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.
If your requirement has been solved please mark your thread as Solved.
In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".
Kindly use [FORMULA] or [CODE] tags when posting your code.
Regards,
Sarang
Dear Hamesh,
Thank you for your effort. It need little changes to the formula. I have added few more links to the sample sheet and applied your formula which is not giving perfect result like above URL. Basically it is not ignoring %. I have highlighted the result. Could you please look into it and change the formula accordingly.
Thanks in advance.
Waiting for your reply.
PFA
Dear Saarang,Check if this one works..Formula:Please Login or Register to view this content.
Thank you for your suggestion but this formula is not working.
Regards
Actually it is not ignoring %. Please find the recent attachment and you will come to know.
Hi,
You're input strings are of different patterns and each pattern would require a different SUBSTITUTE formula, not every pattern can be checked within the same formula all at once
But if we can ignore % then problem will be solved
you can ignore all strings that appear constantly but you need to use helper column for that hope attached one helps
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks