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