extracting data from URL

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Give this a whirl if the URL is in A1:

=LEFT(RIGHT(A1,LEN(A1)-FIND("pId=",A1)-3),FIND("&",RIGHT(A1,LEN(A1)-FIND("pId=",A1)-3))-1)

Or this if pId is always 8 digits:

=MID(A1,FIND("pId=",A1)+4,8)
 
Last edited:
Upvote 0
Hi am having a data of around 5000 URL, wherein i want to extract data from that title, for e.g from the below URL I want to extract only the PID, and i.e 31595017.....i mean to say any formula which can search for "piD" and xtract till the numerical ID.....ie. 31595017

http://offers.cnet.com/redir?tag=ls...&ttag=cnetapi&edId=3&oid=4014-6499_9-31595017
Please help:confused:

Hi am having a data of around 5000 URL, wherein i want to extract data from that title, for e.g from the below URL I want to extract only the PID, and i.e 31595017.....i mean to say any formula which can search for "piD" and xtract till the numerical ID.....ie. 31595017<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
http://offers.cnet.com/redir?tag=lst&pg=1&stype=mplevel_desc&siteId=9&channelid=3000&lop=lst&merId=300346&ontId=6499&pId=31595017&prc=$314.99&sorder=0&ttag=cnetapi&edId=3&oid=4014-6499_9-31595017<o:p></o:p>
Please help<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=Picture_x0020_1 style="VISIBILITY: visible; WIDTH: 12pt; HEIGHT: 15.75pt; mso-wrap-style: square" alt="http://www.mrexcel.com/forum/images/smilies/confused.gif" type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="confused" src="file:///C:\DOCUME~1\ANSARI~1.IMR\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><o:p></o:p>
<o:p></o:p>
The above URL contains a text “pId”, following with the numerical code i.e 31595017, I have marked it Bold. Am having a data of around 10,000 URL, wherein the url might contains the pId. So I just want the formula which search the Text “pId”, and if found then it returns only the number followed by pId…<o:p></o:p>
In simple wants to extract only the numerical code between “pid=” to &prc.<o:p></o:p>
 
Upvote 0
If you do not want to get into complications using a formula or VBA, one simple thing you could do is to use Text to Columns with an ampersand (&) as a delimiter.
 
Upvote 0
:p
Give this a whirl if the URL is in A1:

=LEFT(RIGHT(A1,LEN(A1)-FIND("pId=",A1)-3),FIND("&",RIGHT(A1,LEN(A1)-FIND("pId=",A1)-3))-1)

Or this if pId is always 8 digits:

=MID(A1,FIND("pId=",A1)+4,8)

Hi,

Thanks a lot, the first one works the length of pId is not consistent so the second one won't work......
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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