Monday, July 13, 2009

[code] How to encode URL in Excel

1. (from your worksheet) Tools ... Macros ... Visual Basic Editor
2. (from VB Editor) Insert ... Module
3. (in module window) Paste in the URLEncode function below and save
4. Close VB Editor to return to worksheet, and use URLEncode like any other formulas. For example =URLEncode(B1)


Function URLEncode(ByVal Text As String) As String
Dim i As Integer
Dim acode As Integer
Dim char As String
URLEncode = Text
For i = Len(URLEncode) To 1 Step -1
acode = Asc(Mid$(URLEncode, i, 1))
Select Case acode
Case 48 To 57, 65 To 90, 97 To 122
' don't touch alphanumeric chars
Case 32
' replace space with "+"
Mid$(URLEncode, i, 1) = "+"
Case Else
' replace punctuation chars with "%hex"
URLEncode = Left$(URLEncode, i-1) & "%" & Hex$(acode) & Mid$(URLEncode, i+1)
End Select
Next
End Function

From http://www.devx.com/vb2themax/Tip/19160

8 comments:

  1. Thank you! You just saved me a huge amount of time.

    ReplyDelete
  2. Hi Wandy,

    Was looking for a URL Encode and yours showed up as the first one on Google Search - Nice Surprise.

    Thanks - Ben

    ReplyDelete
  3. Wow, thank you--this saved my toosh (and a ton of time)!

    Katherine

    ReplyDelete
  4. great tutorial.
    i made this online url encoder/decoder in case someone needs it: http://urlencoder.net/

    ReplyDelete
  5. What would the code have to be to only encode the query string portion of the URL, ie, only after the "?" ?

    ReplyDelete
  6. Ok, I know this post is pretty old, but it's a huge help for something I'm working on. Just have one question, is there a way to make the script UTF-8 compliant?

    ReplyDelete