엑셀에서 날짜 값만 추출하기 [vba]
|
어쩌구 101231 저쩌구 |
|
150301 어쩌구 저쩌구 |
|
이러쿵저러쿵120722 |
|
999999070809285000 |
|
sadfb][fdb[osjtr'ns'noj\[jewr120303 |
|
10-10-23aaaaaaaaaaaaaaaaaa |
|
aaaaaaaaaaaaaa16/01/23aaaa |
|
조합된날짜16 02/05 |
|
잘못된날짜16 02 32 |
|
16/13/23aaaaaaaaaaaaaaaaaa |
|
fdsai998jf9j834jf984 |
1. 개발 모드 및 VBA
집에 있는 파일정리를 하다 보니 파일명을 따로 정리하려고 하다가 얻은 방법입니다.
엑셀에 위와 같은 값들이 있을 때 날짜 값만 뽑아오고 싶을 때 사용하는 방법입니다.
일반 수식으로만 구현해보려 해도 너무 복잡하게 되거나 거의 불가능할 것 같아 포기하고 vba를 이용하기로 했습니다.
엑셀 VBA를 이용해야 하니.. 익숙하지 않으신 분들은 넘겨주세요.
파일-옵션에서 먼저 "개발 도구"를 활성화 시켜줍니다.
그러고 이제 개발 도구 탭에서 Visual Basic 으로 들어가거나 Alt+F11 을 눌러줍니다.
이런 창이 나오는데 "삽입 - 모듈" 로 들어갑니다.
똑같은 흰 바탕에 아래와 같이 좌측에 트리에 모듈이 추가 되어있습니다.
2. Regular Expression 기능 추가하기
그런데 저희는 Regular Expression (REGEX:정규식) 을 사용해야 하니까…
"도구-참조"로 들어갑니다.
아래와 같이 Microsoft VBScript Regular Expressions 5.5 를 체크해주고 확인을 눌러줍니다.
위와 같이 하지 않으시면 나중에 사용하려고 하면 아래와 같은 에러메세지만 보실 수 있습니다.
컴파일 오류입니다:
사용자 정의 형식이 정의되지 않았습니다.
3. 코드 사용하기
자 이제 아래와 같은 코드를 삽입해 줍니다.
Public Function dateCheck(rawText As String)
Dim reg As New RegExp
Dim matchStr, matchStrs
reg.Pattern = "(([0][6-9]|[1][0-6])(\s|-|\.|/)?([0][1-9]|[1][0-2])(\s|-|\.|/)?([0-2][0-9]|[3][0-1]))"
Set matchStrs = reg.Execute(rawText)
Set matchStr = matchStrs(0)
dateCheck = False
If reg.Test(rawText) Then
matchStr = Replace(matchStr, " ", "")
matchStr = Replace(matchStr, ".", "")
matchStr = Replace(matchStr, "-", "")
matchStr = Replace(matchStr, "/", "")
dateCheck = matchStr
End If
End Function
4. 코드 설명
중간의 이 부분이 포인트입니다.
reg.Pattern = "(([0][6-9]|[1][0-6])(\s|-|\.|/)?([0][1-9]|[1][0-2])(\s|-|\.|/)?([0-2][0-9]|[3][0-1]))"
제가 하고 싶던 부분은 아래와 같았습니다.
06년01월01일 자료부터 16년12월31일일까지 날짜를 취합하고 각 날짜 두 글자 사이에
공백 - . / 이 들어가도 포함하여 검색하는 것이었습니다.
Regular expression 을 조금 더 단계적으로 만들어보면
1. 년도 표현
먼저 가장 쉽게 0으로 시작하는 년도를 표현하면
[0][6-9]
이 됩니다.
[] 안에 들어간 숫자가 한칸에 어떤 숫자가 들어갈 수 있는지를 표현하는 겁니다.
[5-9] 라면 5,6,7,8,9 를 말하는 것이고 [0-2] 이면 0,1,2 [0-9] 면 숫자를 말하는 겁니다.
2. 월 표현
그럼 또 0으로 시작하는 월을 표현하면
[0][1-9] 가 되겠네요. 00월은 없으니까 1-9만 표현하면 됩니다.
3. 일 표현
그리고 마지막으로 날짜는 1~31일까지지만 편하게 표현해 보도록 하겠습니다.
[0-3][1-9]
4. 년월일 조합
[0][6-9][0][1-9][0-3][1-9]
이렇게 하면 기본적인 세트는 만들어집니다.
그럼 10년부터 16년까지는 [1][0-6], 10월부터 12월은 [1][0-2] 이 되겠네요.
자 그럼 두 가지를 조합하기 위해서는 | 가 필요합니다. 백스페이스 근처에 있는 \ 위의 |
이 문자가 regex에서 or을 표현합니다. 그리고 수학에서의 괄호()를 쓰면 됩니다.
그럼 앞서 말한 0으로 시작하는 년도와 0으로 시작하는 월을 조합하면 되겠군요.
년1 [0][6-9]
년2 [1][0-6]
월1 [0][1-9]
월2 [1][0-2]
일1 [0-3][0-9]
년 월일을 각자 |로 조합하면
년 = ([0][6-9]|[1][0-6])
월 = ([0][1-9]|[1][0-2])
일 = [0-3][0-9]
내친김에 월도 31일까지만으로 구분해 볼까요. 00일이 포함되긴 하지만 귀찮네요...
일 = ([0-2][0-9]|[3][0-1])
자 이제 이걸 그냥 갔다 붙이면 년 월 일을 찾는 regex가 나옵니다.
([0][6-9]|[1][0-6])([0][1-9]|[1][0-2])([0-2][0-9]|[3][0-1])
그런데 이렇게 하면 중간에 띄어쓰기나 등등등이 들어가면 못찾게 되죠.
5. 구분자 찾기
다른 문자가 들어갈 경우도 있지만 흔히들 날짜 구분으로 사용하는
띄어쓰기, 점(.), 슬래시(/), 마이너스(-)만 고려하여 추가를 하고 싶다면 아래를 각각의 년 월 일 사이에 넣어 주면됩니다.
(\s|-|\.|/)?
먼저 쓰고 설명을 드리면
그냥 s를 쓰면 s를 찾을거고 그냥 .을 쓰면 다른 의미가 들어가기 때문에 이를 구분하기 위해
역슬래시(한화) \ 표시를 사용합니다.
\s는 공백 \. 은 점 을 의미하는 것이고 마이너스(-)와 슬래시(/)는 그냥 사용하시면 됩니다.
이걸 중간에 | 를 넣었으니 결국
(공백 or - or . or / ) 의 의미가 되는 것인데 뒤의 ?는 쓰거나 말거나 입니다.
150303 이나 15 03 03 이나 모두 찾겠다는 얘기죠.
6. 구분자 삭제
그런데! 통일성을 만들기 위해 아래와 같은 함수를 또 사용했습니다.
앞서 찾은 공백,점,슬래시,마이너스를 그냥 없애버리겠다는 뜻입니다.
matchStr = Replace(matchStr, " ", "")
matchStr = Replace(matchStr, ".", "")
matchStr = Replace(matchStr, "-", "")
matchStr = Replace(matchStr, "/", "")
060301 이나 06-03-01 이나 모두 060301로 통일되서 출력된다는 거죠.
5. 결과
위와 같이 쓴 뒤 엑셀 수식에서 앞서 정의한 Function 명대로 사용하시면 됩니다.
그대로 복붙하면
|
어쩌구 101231 저쩌구 |
101231 |
|
150301 어쩌구 저쩌구 |
150301 |
|
이러쿵저러쿵120722 |
120722 |
|
999999070809285000 |
070809 |
|
sadfb][fdb[osjtr'ns'noj\[jewr120303 |
120303 |
|
10-10-23aaaaaaaaaaaaaaaaaa |
101023 |
|
aaaaaaaaaaaaaa16/01/23aaaa |
160123 |
|
조합된날짜16 02/05 |
160205 |
|
잘못된날짜16 02 32 |
#VALUE! |
|
16/13/23aaaaaaaaaaaaaaaaaa |
#VALUE! |
|
fdsai998jf9j834jf984 |
#VALUE! |
마지막 세개가 잘못된 이유는 32일 / 13월 / 날짜없음. 이렇게 세 가지 입니다.
이러면 왠만한 날짜 값은 그대로 가져올 수 있게 됩니다.
매크로가 들어간 엑셀파일 저장시에는 꼭 파일형식을 Excel 매크로 사용 통합 문서를 선택해 주셔야 합니다.
