CODEWITHSUNDEEP
regexurlgoogle-sheetsgoogle-sheets-formulaarray-formulas
Ashok
Ashok

Reputation: 69

Bing search Link to underlying link in Google Sheets

We get links like this: 

http://www.bing.com/news/apiclick.aspx?ref=FexRss&aid=&tid=23A2D3B3B1FC42ADB33E0D6A07F0E740&url=https%3a%2f%2ftimesofindia.indiatimes.com%2fgadgets-news%2fwhatsapp-for-android-finally-gets-fingerprint-security%2farticleshow%2f71842024.cms&c=7798011707021326482&mkt=en-us

What formula can I use in Google Sheets to get the underlying link:

https://timesofindia.indiatimes.com/gadgets-news/whatsapp-for-android-finally-gets-fingerprint-security/articleshow/71842024.cms

Upvotes: 2

Views: 79

Answers (1)

player0
player0

Reputation: 1

try:

=SUBSTITUTE(SUBSTITUTE(REGEXEXTRACT(
 REGEXEXTRACT(A1, "bing.com(.+)"), "http[^&]+"), "%3a", ":"), "%2f", "/")

0

and for array:

=ARRAYFORMULA(IFNA(SUBSTITUTE(SUBSTITUTE(REGEXEXTRACT(
 REGEXEXTRACT(A20:A21, "bing.com(.+)"), "http[^&]+"), "%3a", ":"), "%2f", "/")))

Upvotes: 2

Related Questions