🔍 정규 표현식으로 데이터 마스터하기: 추출부터 변환까지! 🚀

🤔 정규 표현식이 뭐길래?

정규 표현식, 줄여서 정규식(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]"),"유효한 전화번호가 아닙니다")

우와, 이건 좀 복잡해 보이죠? 😅 하나씩 뜯어볼게요:

1. SUBSTITUTE 함수로 "-"와 공백을 제거합니다.
2. FILTERXML 함수를 사용해 정규식과 비슷한 기능을 구현합니다.
3. 숫자만 남기고 다른 문자는 모두 제거합니다.
4. 길이가 10자리(일반 전화번호) 또는 11자리(휴대폰 번호)인 경우만 추출합니다.

이렇게 하면 다양한 형식의 전화번호를 깔끔하게 정리할 수 있어요. 완전 천재 같지 않나요? 🧠✨

💳 신용카드 번호 마스킹하기

고객의 개인정보 보호는 정말 중요하죠. 신용카드 번호의 일부를 가리는 작업을 해볼까요?

=REGEXREPLACE(A1, "(\d{4})(\d{4})(\d{4})(\d{4})", "$1-XXXX-XXXX-$4")

이 함수는 16자리의 신용카드 번호를 받아서 중간의 8자리를 'X'로 마스킹합니다. 보안 담당자가 엄지척 👍 할 만한 작업이죠!

🔄 정규식으로 데이터 변환하기

데이터 추출도 멋지지만, 변환은 더 멋져요! 정규식으로 데이터를 원하는 형태로 바꿔볼까요?

📅 날짜 형식 변환하기

다양한 형식의 날짜를 일관된 형식으로 바꾸고 싶다면 어떻게 해야 할까요? 예를 들어, "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일"),
      "유효하지 않은 날짜 형식"
    )
  )
)

우와, 이건 정말 대단해요! 😲 이 함수는 다음과 같은 작업을 수행합니다:

1. REGEXMATCH로 날짜 형식을 확인합니다.
2. REGEXREPLACE로 날짜 구성 요소를 추출합니다.
3. DATEVALUE로 날짜 값으로 변환합니다.
4. TEXT 함수로 원하는 형식으로 포맷팅합니다.

이렇게 하면 다양한 형식의 날짜를 모두 통일된 한국식 날짜 표기로 변환할 수 있어요. 완전 편리하죠? 👏

🌐 URL에서 도메인 추출하기

웹사이트 주소에서 도메인만 깔끔하게 뽑아내고 싶다면 어떻게 해야 할까요? 정규식으로 아주 쉽게 할 수 있답니다!

=REGEXEXTRACT(A1, "(?:https?:\/\/)?(?:www\.)?([^\/]+)")

이 함수는 다음과 같은 작업을 수행해요:

• (?:https?:\/\/)? - http:// 또는 https://가 있을 수도, 없을 수도 있어요.
• (?:www\.)? - www.가 있을 수도, 없을 수도 있죠.
• ([^\/]+) - 슬래시(/) 전까지의 모든 문자를 캡처합니다. 이게 바로 도메인이에요!

이 함수를 사용하면 "https://www.example.com/page"에서 "example.com"만 깔끔하게 추출할 수 있어요. 완전 프로 같지 않나요? 😎

🎨 정규식으로 데이터 정리하기

데이터 분석을 하다 보면 '지저분한' 데이터를 만나는 경우가 많죠. 정규식은 이런 데이터를 깔끔하게 정리하는 데에도 아주 유용해요!

🧹 불필요한 공백 제거하기

데이터에 불필요한 공백이 잔뜩 있다면? 정규식으로 한 방에 해결할 수 있어요!

=TRIM(REGEXREPLACE(A1, "\s+", " "))

이 함수는 다음과 같은 작업을 수행합니다:

1. REGEXREPLACE(A1, "\s+", " ") - 연속된 공백을 하나의 공백으로 바꿉니다.
2. TRIM() - 문자열의 앞뒤 공백을 제거합니다.

이렇게 하면 "Hello World !"가 "Hello World!"로 깔끔하게 변합니다. 완전 시원시원하죠? 💨

🔤 특수 문자 제거하기

텍스트에서 알파벳과 숫자를 제외한 모든 특수 문자를 제거하고 싶다면 어떻게 해야 할까요?

=REGEXREPLACE(A1, "[^a-zA-Z0-9]", "")

이 함수는 알파벳과 숫자가 아닌 모든 문자를 빈 문자열로 대체합니다. 예를 들어, "Hello, World! 123"은 "HelloWorld123"이 되죠. 깔끔하고 심플해요! ✨

🚀 정규식 활용 꿀팁

정규식을 더 효과적으로 사용하기 위한 몇 가지 팁을 알려드릴게요!

🎭 정규식의 실전 활용 사례

지금까지 배운 내용을 실제 비즈니스 상황에 적용해볼까요? 여러분이 데이터 분석가라고 상상해보세요. 다음과 같은 상황에서 정규식을 어떻게 활용할 수 있을까요?

📊 상품 코드 추출하기

온라인 쇼핑몰에서 상품 데이터를 분석하고 있다고 가정해봅시다. 상품명에 포함된 상품 코드를 추출해야 하는 상황이에요. 상품 코드는 항상 대문자 알파벳 2개와 숫자 4개로 구성되어 있답니다.

=REGEXEXTRACT(A1, "[A-Z]{2}\d{4}")

이 함수를 사용하면 "멋진 청바지 AB1234 신상품"에서 "AB1234"만 깔끔하게 추출할 수 있어요. 엄청 편리하죠? 😃

📧 이메일 주소 유효성 검사

고객 데이터베이스에 있는 이메일 주소가 올바른 형식인지 확인해야 한다고 해볼까요? 정규식으로 아주 쉽게 할 수 있어요!

=REGEXMATCH(A1, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")

이 함수는 이메일 주소가 올바른 형식인지 확인하고 TRUE 또는 FALSE를 반환합니다. 완전 프로페셔널하죠? 👨‍💼👩‍💼

💬 해시태그 추출하기

소셜 미디어 데이터를 분석하고 있다고 가정해볼까요? 포스트에서 해시태그만 추출하고 싶다면 어떻게 해야 할까요?

=REGEXEXTRACT(A1, "#\w+")

이 함수를 사용하면 "오늘 날씨가 정말 좋아요! #날씨 #행복 #주말"에서 모든 해시태그를 추출할 수 있어요. 소셜 미디어 트렌드 분석이 한결 쉬워지겠죠? 📈

🏋️‍♀️ 정규식 실력 향상을 위한 연습 문제

정규식 실력을 더 키우고 싶으신가요? 여기 몇 가지 연습 문제를 준비했어요. 도전해보세요!

문제 1: 다음 문자열에서 모든 숫자를 추출하세요.

"안녕하세요123, 반갑습니다456!"

힌트: \d를 사용해보세요.