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?
3 Answers
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.
Comments
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:
- 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=2a column title ofup_idwill be incorrectly1but&up_idwill be2as intended. - any non-plain-english values would probably be encoded, but there's no native excel function to decode them. so
a%20valuewill NOT be changed toa value.
usage example: https://docs.google.com/spreadsheets/d/1DuUa8PWdf07PnZzFNouGF-byGs-3ygH44DvJbvdR00Q/edit?usp=sharing
Comments
=LEFT(MID(A1,SEARCH("id=",A1)+3,15),SEARCH("&",MID(A1,SEARCH("id=",A1)+3,15))-1)
