맥(Mac) 에서 엑셀 URL Encoding 하기

Posted on In Word count in article: 740 Reading time ≈ 3 mins.

엑셀에서 ENCODEURL 기능이 있어요

URL 을 Encoding 하려는 데 엑셀에 ENCODEURL 이라는 function 이 있습니다
하지만, MAC 용 엑셀 에서는 안됨

Function: ENCODEURL 여기도 보면

Excel 2013 and later versions for Windows.
Not available in Excel Online and Excel for Mac.

맥과 Online 에서는 안된다고 나와있습니다

맥에서 함수 만들기

없으면 만들어서 사용하면 됩니다

  1. 엑셀을 Macro 사용가능하게 저장하고 (.xlsm)
  2. Tools > Macro > Visual Basic Editor 로 에디터 실행
  3. Visual Basic Editor 에서 new Module 로 모듈 추가
  4. 함수 코드 복사해서 붙여넣기
  5. 만든 함수를 엑셀에서 사용

Encode 함수

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Public Function URLEncode(ByRef txt As String) As String
    Dim buffer As String, i As Long, c As Long, n As Long
    buffer = String$(Len(txt) * 12, "%")
 
    For i = 1 To Len(txt)
        c = AscW(Mid$(txt, i, 1)) And 65535
 
        Select Case c
            Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95  ' Unescaped 0-9A-Za-z-._ '
                n = n + 1
                Mid$(buffer, n) = ChrW(c)
            Case Is <= 127            ' Escaped UTF-8 1 bytes U+0000 to U+007F '
                n = n + 3
                Mid$(buffer, n - 1) = Right$(Hex$(256 + c), 2)
            Case Is <= 2047           ' Escaped UTF-8 2 bytes U+0080 to U+07FF '
                n = n + 6
                Mid$(buffer, n - 4) = Hex$(192 + (c \ 64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
            Case 55296 To 57343       ' Escaped UTF-8 4 bytes U+010000 to U+10FFFF '
                i = i + 1
                c = 65536 + (c Mod 1024) * 1024 + (AscW(Mid$(txt, i, 1)) And 1023)
                n = n + 12
                Mid$(buffer, n - 10) = Hex$(240 + (c \ 262144))
                Mid$(buffer, n - 7) = Hex$(128 + ((c \ 4096) Mod 64))
                Mid$(buffer, n - 4) = Hex$(128 + ((c \ 64) Mod 64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
            Case Else                 ' Escaped UTF-8 3 bytes U+0800 to U+FFFF '
                n = n + 9
                Mid$(buffer, n - 7) = Hex$(224 + (c \ 4096))
                Mid$(buffer, n - 4) = Hex$(128 + ((c \ 64) Mod 64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
        End Select
    Next
    URLEncode = Left$(buffer, n)
End Function

Decode 함수

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Function URLDecode(ByVal strIn)
    On Error Resume Next
    Dim sl&, tl&, key$, kl&
    sl = 1:    tl = 1: key = "%": kl = Len(key)
    sl = InStr(sl, strIn, key, 1)
    Do While sl > 0
        If (tl = 1 And sl <> 1) Or tl < sl Then
            URLDecode = URLDecode & Mid(strIn, tl, sl - tl)
        End If
        Dim hh$, hi$, hl$, a$
        Select Case UCase(Mid(strIn, sl + kl, 1))
            Case "U"    'Unicode URLEncode
                a = Mid(strIn, sl + kl + 1, 4)
                URLDecode = URLDecode & ChrW("&H" & a)
                sl = sl + 6
            Case "E"    'UTF-8 URLEncode
                hh = Mid(strIn, sl + kl, 2)
                a = Int("&H" & hh)    'ascii?
                If Abs(a) < 128 Then
                    sl = sl + 3
                    URLDecode = URLDecode & Chr(a)
                Else
                    hi = Mid(strIn, sl + 3 + kl, 2)
                    hl = Mid(strIn, sl + 6 + kl, 2)
                    a = ("&H" & hh And &HF) * 2 ^ 12 Or ("&H" & hi And &H3F) * 2 ^ 6 Or ("&H" & hl And &H3F)
                    If a < 0 Then a = a + 65536
                    URLDecode = URLDecode & ChrW(a)
                    sl = sl + 9
                End If
            Case Else    'Asc URLEncode
                hh = Mid(strIn, sl + kl, 2)    '??
                a = Int("&H" & hh)    'ascii?

                If Abs(a) < 128 Then
                    sl = sl + 3
                Else
                    hi = Mid(strIn, sl + 3 + kl, 2)    '??
                    'a = Int("&H" & hh & hi) '?ascii?
                    a = (Int("&H" & hh) - 194) * 64 + Int("&H" & hi)
                    sl = sl + 6
                End If
                URLDecode = URLDecode & ChrW(a)
        End Select
        tl = sl
        sl = InStr(sl, strIn, key, 1)
    Loop
    URLDecode = URLDecode & Mid(strIn, tl)
End Function

함수등록화면

Visual Basic Editor 에서 함수 등록

기타

아래 함수도 찾았지만 맥에서는 실행이 안되었습니다
윈도우에서는 될 수도 있어서 남겨놓습니다

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Function ENCODEURL(varText As Variant, Optional blnEncode = True)
Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
      Set objHtmlfile = CreateObject("htmlfile")
      With objHtmlfile.parentWindow
        .execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
      End With
    End If
    If blnEncode Then
      ENCODEURL = objHtmlfile.parentWindow.encode(varText)
    End If
End Function

Function DECODEURL(varText As Variant, Optional blnEncode = True)
Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
      Set objHtmlfile = CreateObject("htmlfile")
      With objHtmlfile.parentWindow
        .execScript "function decode(s) {return decodeURIComponent(s)}", "jscript"
      End With
    End If
    If blnEncode Then
      DECODEURL = objHtmlfile.parentWindow.decode(varText)
    End If
End Function

Encoding 변환표

기호 Encoding
! 21%
# 23%
$ 24%
% 25%
& 26%
‘ 27%
( 28%
) 29%
* %2A
+ %2B
, %2C
/ %2F
: %3A
; %3B
= %3D
? %3F
@ 40%
[ %5B
] %5D
newline %0A or %0D or %0D%0A
space 20%
“ 22%
% 25%
- %2D
. %2E
< %3C
> %3E
\ %5C
^ %5E
_ %5F
` 60%
{ %7B
` `
} %7D
~ %7E

참고사이트

abuyasmeen.com