Extract data from a List of URLs

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
Hi

I am trying to extract some data from a list of URLs, but NOT sure how to do it.

I have a list of urls in column A Sheet1. I can not work out how to write a code that would extract the bit in RED.

href="/s/ref=sr_in_-2_p_6_0?fst=as%3Aoff&rh=n%3A318949011%2Cn%3A%21319530011%2Cn%3A319535011%2Cp_6%3AA3DA3SWCURNAWD&bbn=319535011&ie=UTF8&qid=1600344355&rnid=401720011"

All the urls will be as the one above, I only need the red part. The list of urls will be dynamic

I would like the data to go in to Sheet1 Column B

Thanks in advance
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
You could put other examples.
Use the XL2BB tool to paste a range of cells here.

If the text to extract is always before the second "&" symbol and it is always 14 characters, then you could try the following:

Libro2
AB
1
2href="/s/ref=sr_in_-2_p_6_0?fst=as%3Aoff&rh=n%3A318949011%2Cn%3A%21319530011%2Cn%3A319535011%2Cp_6%3AA3DA3SWCURNAWD&bbn=319535011&ie=UTF8&qid=1600344355&rnid=401720011"A3DA3SWCURNAWD
Hoja3
Cell Formulas
RangeFormula
B2B2=MID(A2,FIND("&",A2,FIND("&",A2)+1)-14,14)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
with Power Query
source
A3DA3SWCURNAWD

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table23"]}[Content],
    TBD = Table.TransformColumns(Source, {{"source", each Text.RemoveRange(Text.BetweenDelimiters(_, "%", "&", {0, RelativePosition.FromEnd}, 0),0,2), type text}})
in
    TBD
 

Watch MrExcel Video

Forum statistics

Threads
1,127,593
Messages
5,625,687
Members
416,127
Latest member
MALEPINZON

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
Top