URL 디코딩 엑셀 매크로 코드 (URL Decoding Excel Macro Code)

보안장비(IPS, IDS 등) 중 정책에서 URL 인코딩된 패턴 문자열을 디코딩할 경우 유용하며 업무상 패턴 문자열을 공부하고자 일괄적으로 디코딩함.

보통 정책을 엑셀로 출력이 가능할 경우 아래와 같이 매크로 코드를 돌려서 디코딩하면 됨.

참고로 URL 디코딩해주는 웹 페이지나 툴로 돌릴 경우 리소스 문제로 결과값이 출력되지 않는 경우가 있음.

1. Excel > 개발 도구 > Visual Basic 클릭

(상단에 개발 도우가 없을 경우 Excel > 파일 > 옵션 >  리본 사용자 지정 > 개발 도구 옵션 선택, 안 보이더라도 단축키 Alt+F11 누르면 됨)

2. URL 디코딩할 시트를 선택한 후 오른쪽 마우스의 삽입 > 모듈 클릭

3. 아래 매크로 코드를 복사 붙여넣기 한 후 실행 버튼(F5) 클릭

참고로 아스키 코드 중 육안으로 확인되는 문자열에 한에서만 URL 디코딩시킴.

Sub URL_Decoding_Excel_Macro_Code()       Cells.Replace What:="%20", Replacement:=" ", LookAt:=xlPart, _         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _         ReplaceFormat:=False     Cells.Replace What:="%21", Replacement:="!", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%22", Replacement:="""", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%23", Replacement:="#", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%24", Replacement:="$", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%25", Replacement:="%", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%26", Replacement:="&", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%27", Replacement:="'", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%28", Replacement:="(", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%29", Replacement:=")", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%2A", Replacement:="*", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%2B", Replacement:="+", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%2C", Replacement:=",", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%2D", Replacement:="-", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%2E", Replacement:=".", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%2F", Replacement:="/", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False       Cells.Replace What:="%30", Replacement:="0", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%31", Replacement:="1", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%32", Replacement:="2", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%33", Replacement:="3", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%34", Replacement:="4", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%35", Replacement:="5", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%36", Replacement:="6", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%37", Replacement:="7", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%38", Replacement:="8", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%39", Replacement:="9", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%3A", Replacement:=":", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%3B", Replacement:=";", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%3C", Replacement:="<", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%3D", Replacement:="=", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%3E", Replacement:=">", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%3F", Replacement:="?", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False       Cells.Replace What:="%40", Replacement:="@", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%41", Replacement:="A", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%42", Replacement:="B", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%43", Replacement:="C", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%44", Replacement:="D", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%45", Replacement:="E", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%46", Replacement:="F", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%47", Replacement:="G", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%48", Replacement:="H", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%49", Replacement:="I", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%4A", Replacement:="J", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%4B", Replacement:="K", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%4C", Replacement:="L", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%4D", Replacement:="M", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%4E", Replacement:="N", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%4F", Replacement:="O", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False       Cells.Replace What:="%50", Replacement:="P", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%51", Replacement:="Q", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%52", Replacement:="R", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%53", Replacement:="S", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%54", Replacement:="T", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%55", Replacement:="U", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%56", Replacement:="V", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%57", Replacement:="W", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%58", Replacement:="X", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%59", Replacement:="Y", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%5A", Replacement:="Z", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%5B", Replacement:="[", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%5C", Replacement:="\", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%5D", Replacement:="]", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%5E", Replacement:="^", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%5F", Replacement:="_", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False       Cells.Replace What:="%60", Replacement:="`", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%61", Replacement:="a", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%62", Replacement:="b", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%63", Replacement:="c", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%64", Replacement:="d", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%65", Replacement:="e", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%66", Replacement:="f", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%67", Replacement:="g", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%68", Replacement:="h", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%69", Replacement:="i", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%6A", Replacement:="j", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%6B", Replacement:="k", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%6C", Replacement:="l", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%6D", Replacement:="m", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%6E", Replacement:="n", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%6F", Replacement:="o", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False       Cells.Replace What:="%70", Replacement:="p", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%71", Replacement:="q", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%72", Replacement:="r", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%73", Replacement:="s", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%74", Replacement:="t", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%75", Replacement:="u", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%76", Replacement:="v", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%77", Replacement:="w", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%78", Replacement:="x", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%79", Replacement:="y", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%7A", Replacement:="z", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%7B", Replacement:="{", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%7C", Replacement:="|", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%7D", Replacement:="}", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False     Cells.Replace What:="%7E", Replacement:="~", LookAt:=xlPart, SearchOrder _         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub

============================================================

본 게시물은 KOROMOON 님께서 작성하였으며 CCL (Creative Commons License) 에서 "저작자표시-비영리-동일조건변경허락" 이용조건으로 자료를 이용하셔야 합니다.