CODEWITHSUNDEEP
excelformula
Paul P
Paul P

Reputation: 41

Parse url string in excel

I'd like to parse a number from a url string in Excel and paste it in another cell using a formula. For example, I have https://system.google.com/childrecords.nl?id=668&t=CustomField&rectype=-704&weakdeps=T. I would like to use a formula to pull out '668' and place that in an adjacent cell. Any ideas on how to go about this?

Upvotes: 0

Views: 3587

Answers (3)

oriadam
oriadam

Reputation: 8559

i use the following formula:

=MID($A2,FIND(B$1&"=",$A2)+LEN(B$1)+1,FIND("&",$A2,FIND(B$1&"=",$A2)+LEN(B$1))-FIND(B$1&"=",$A2)-LEN(B$1)-1)

assuming the URL is on column A and the column title is the desired parameter.

$A2 = the URL string
B$1 = parameter name

the $ is on the correct place to allow copying the formula to several columns and all rows.

known cavities:

  1. you'll have to prefix short parameter names with & if the name is a part of another parameter's name. for example in ?popup_id=1&up_id=2 a column title of up_id will be incorrectly 1 but &up_id will be 2 as intended.
  2. any non-plain-english values would probably be encoded, but there's no native excel function to decode them. so a%20value will NOT be changed to a value.

usage example: https://docs.google.com/spreadsheets/d/1DuUa8PWdf07PnZzFNouGF-byGs-3ygH44DvJbvdR00Q/edit?usp=sharing

Upvotes: 1

Brian
Brian

Reputation: 2108

If you always want the first set of numbers and there is always an = sign and an & on either side then this will work:

=MID(A1,SEARCH("=",A1)+1,SEARCH("&",A1)-SEARCH("=",A1)-1)

Otherwise, here's a UDF that would work. You would paste this in a regular module in the Visual Basic Editor Alt+F11 and use it like any other formula:

Option Explicit

Public Function FindFirstSetOfNumbers(MyRange As Range) As String

Dim NumCounter As Integer
Dim Start As Integer

Start = Application.Evaluate("=MIN(FIND({0,1,2,3,4,5,6,7,8,9}," & MyRange.Address & "&""0123456789""))")

For NumCounter = Start To Len(MyRange)
    If IsNumeric(Mid(MyRange, NumCounter, 1)) Then
        FindFirstSetOfNumbers = FindFirstSetOfNumbers & Mid(MyRange, NumCounter, 1)
    Else
        Exit For
    End If
Next NumCounter

End Function

Just type =FindFirstSetOfNumbers(YourRange) in any cell and press enter, or =VALUE(FindFirstSetOfNumbers(YourRange)) to convert it to a number data type.

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166316

=LEFT(MID(A1,SEARCH("id=",A1)+3,15),SEARCH("&",MID(A1,SEARCH("id=",A1)+3,15))-1)

Upvotes: 0

Related Questions