🔍 정규 표현식으로 데이터 마스터하기: 추출부터 변환까지! 🚀
안녕하세요, 데이터 마법사들! 🧙♂️✨ 오늘은 엑셀에서 정규 표현식을 사용해 데이터를 추출하고 변환하는 초특급 꿀팁을 알려드릴게요. 이 스킬만 있으면 여러분도 데이터 정리의 달인이 될 수 있답니다! 😎
재능넷에서 엑셀 고수들의 강의를 들어보셨나요? 아직 안 들어보셨다면 꼭 한번 체크해보세요! 여기서 배우는 내용과 함께 활용하면 여러분의 데이터 스킬이 폭발적으로 상승할 거예요! 💥
🤔 정규 표현식이 뭐길래?
정규 표현식, 줄여서 정규식(Regex)이라고도 불리는 이 녀석은 문자열에서 특정 패턴을 찾거나 매칭할 때 사용하는 강력한 도구예요. 마치 텍스트 속에서 보물을 찾는 탐험가처럼, 원하는 정보를 정확하게 찾아낼 수 있죠!
🎭 정규식의 매력 포인트:
- 복잡한 패턴도 간단하게 표현 가능
- 대량의 데이터에서 빠르게 정보 추출
- 텍스트 형식 검증에 완전 굿!
- 프로그래밍 언어와 툴에서 널리 사용됨
엑셀에서도 이 정규식을 활용할 수 있다니, 완전 대박이죠? 🎉 이제 우리가 어떻게 이 마법의 도구를 사용할 수 있는지 알아볼까요?
🛠 엑셀에서 정규식 사용하기
엑셀에서 정규식을 사용하려면 몇 가지 함수들이 필요해요. 주로 FILTERXML, SUBSTITUTE, REGEXEXTRACT (Google Sheets) 등의 함수를 조합해서 사용하게 됩니다.
🚨 주의사항: 엑셀 버전에 따라 사용할 수 있는 함수가 다를 수 있어요. 최신 버전을 사용하고 있다면 더 많은 기능을 활용할 수 있답니다!
자, 이제 본격적으로 정규식의 세계로 들어가볼까요? 준비되셨나요? 3, 2, 1... 출발! 🚀
📚 정규식 기본 문법
정규식을 사용하기 전에 기본적인 문법부터 알아볼게요. 이건 마치 새로운 언어를 배우는 것과 같아요! 🗣️
- . - 모든 문자 하나와 매치
- * - 앞의 패턴이 0번 이상 반복
- + - 앞의 패턴이 1번 이상 반복
- ? - 앞의 패턴이 0 또는 1번 등장
- ^ - 문자열의 시작
- $ - 문자열의 끝
- [ ] - 문자 클래스, 괄호 안의 문자들 중 하나와 매치
- [^ ] - 부정 문자 클래스, 괄호 안의 문자들을 제외한 문자와 매치
- \d - 숫자와 매치
- \w - 단어 문자(알파벳, 숫자, 언더스코어)와 매치
- \s - 공백 문자와 매치
이렇게 보면 좀 복잡해 보이죠? 하지만 걱정 마세요! 실제로 사용해보면 생각보다 쉽답니다. 😉
🎯 정규식으로 데이터 추출하기
자, 이제 실전에 들어가볼까요? 엑셀에서 정규식을 사용해 데이터를 추출하는 방법을 알아봅시다!
🌟 예제 상황: 여러분이 온라인 쇼핑몰의 고객 데이터를 관리하고 있다고 가정해볼게요. 고객들의 이메일 주소에서 도메인만 추출하고 싶다면 어떻게 해야 할까요?
이럴 때 정규식이 빛을 발하죠! 아래의 함수를 사용해보세요:
=IFERROR(RIGHT(A1,LEN(A1)-FIND("@",A1)),"이메일 주소가 아닙니다")이 함수는 "@" 기호 뒤의 모든 문자를 추출합니다. 완전 쉽죠? 😎
하지만 이건 아주 기본적인 방법이에요. 좀 더 복잡한 패턴을 다뤄볼까요?
📞 전화번호 추출하기
고객 데이터에 전화번호가 다양한 형식으로 섞여 있다고 가정해봅시다. 어떤 건 "-"가 있고, 어떤 건 없고, 심지어 국가 코드가 붙어있기도 하죠. 이런 상황에서 정규식은 진가를 발휘합니다!
=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","")&"</s></t>","//s[translate(.,'0','')=''][string-length()=10 or string-length()=11]"),"유효한 전화번호가 아닙니다")우와, 이건 좀 복잡해 보이죠? 😅 하나씩 뜯어볼게요:
SUBSTITUTE함수로 "-"와 공백을 제거합니다.FILTERXML함수를 사용해 정규식과 비슷한 기능을 구현합니다.- 숫자만 남기고 다른 문자는 모두 제거합니다.
- 길이가 10자리(일반 전화번호) 또는 11자리(휴대폰 번호)인 경우만 추출합니다.
이렇게 하면 다양한 형식의 전화번호를 깔끔하게 정리할 수 있어요. 완전 천재 같지 않나요? 🧠✨
💳 신용카드 번호 마스킹하기
고객의 개인정보 보호는 정말 중요하죠. 신용카드 번호의 일부를 가리는 작업을 해볼까요?
=REGEXREPLACE(A1, "(\d{4})(\d{4})(\d{4})(\d{4})", "$1-XXXX-XXXX-$4")이 함수는 16자리의 신용카드 번호를 받아서 중간의 8자리를 'X'로 마스킹합니다. 보안 담당자가 엄지척 👍 할 만한 작업이죠!
⚠️ 주의: REGEXREPLACE 함수는 Google Sheets에서 사용 가능합니다. Microsoft Excel에서는 VBA나 다른 방법을 사용해야 할 수 있어요.
🔄 정규식으로 데이터 변환하기
데이터 추출도 멋지지만, 변환은 더 멋져요! 정규식으로 데이터를 원하는 형태로 바꿔볼까요?
📅 날짜 형식 변환하기
다양한 형식의 날짜를 일관된 형식으로 바꾸고 싶다면 어떻게 해야 할까요? 예를 들어, "YYYY-MM-DD", "MM/DD/YYYY", "DD.MM.YYYY" 등의 형식을 모두 "YYYY년 MM월 DD일" 형식으로 바꿔봅시다!
=ARRAYFORMULA(
IF(REGEXMATCH(A1:A, "^\d{4}[-/\.]\d{2}[-/\.]\d{2}$"),
TEXT(DATEVALUE(REGEXREPLACE(A1:A, "(\d{4})[-/\.](\d{2})[-/\.](\d{2})", "$1-$2-$3")), "YYYY년 MM월 DD일"),
IF(REGEXMATCH(A1:A, "^\d{2}[-/\.]\d{2}[-/\.]\d{4}$"),
TEXT(DATEVALUE(REGEXREPLACE(A1:A, "(\d{2})[-/\.](\d{2})[-/\.](\d{4})", "$3-$1-$2")), "YYYY년 MM월 DD일"),
"유효하지 않은 날짜 형식"
)
)
)우와, 이건 정말 대단해요! 😲 이 함수는 다음과 같은 작업을 수행합니다:
REGEXMATCH로 날짜 형식을 확인합니다.REGEXREPLACE로 날짜 구성 요소를 추출합니다.DATEVALUE로 날짜 값으로 변환합니다.TEXT함수로 원하는 형식으로 포맷팅합니다.
이렇게 하면 다양한 형식의 날짜를 모두 통일된 한국식 날짜 표기로 변환할 수 있어요. 완전 편리하죠? 👏
🌐 URL에서 도메인 추출하기
웹사이트 주소에서 도메인만 깔끔하게 뽑아내고 싶다면 어떻게 해야 할까요? 정규식으로 아주 쉽게 할 수 있답니다!
=REGEXEXTRACT(A1, "(?:https?:\/\/)?(?:www\.)?([^\/]+)")이 함수는 다음과 같은 작업을 수행해요:
(?:https?:\/\/)?- http:// 또는 https://가 있을 수도, 없을 수도 있어요.(?:www\.)?- www.가 있을 수도, 없을 수도 있죠.([^\/]+)- 슬래시(/) 전까지의 모든 문자를 캡처합니다. 이게 바로 도메인이에요!
이 함수를 사용하면 "https://www.example.com/page"에서 "example.com"만 깔끔하게 추출할 수 있어요. 완전 프로 같지 않나요? 😎
🎨 정규식으로 데이터 정리하기
데이터 분석을 하다 보면 '지저분한' 데이터를 만나는 경우가 많죠. 정규식은 이런 데이터를 깔끔하게 정리하는 데에도 아주 유용해요!
🧹 불필요한 공백 제거하기
데이터에 불필요한 공백이 잔뜩 있다면? 정규식으로 한 방에 해결할 수 있어요!
=TRIM(REGEXREPLACE(A1, "\s+", " "))이 함수는 다음과 같은 작업을 수행합니다:
REGEXREPLACE(A1, "\s+", " ")- 연속된 공백을 하나의 공백으로 바꿉니다.TRIM()- 문자열의 앞뒤 공백을 제거합니다.
이렇게 하면 "Hello World !"가 "Hello World!"로 깔끔하게 변합니다. 완전 시원시원하죠? 💨
🔤 특수 문자 제거하기
텍스트에서 알파벳과 숫자를 제외한 모든 특수 문자를 제거하고 싶다면 어떻게 해야 할까요?
