엑셀 함수 몰라도 ChatGPT로 해결하기 - SUBSTITUTE, VBA 매크로

HRD 담당자라면 수강생 모집 시 항상 마주치는 고민 중 하나가 바로 전화번호 등 입력된 데이터 형태의 일관성 부족입니다. 수업 시작 일정에 맞춰 수강생에게 문자를 발송하려 할 때, 전화번호가 '010-1234-5678', '01012345678' 등으로 다양하게 입력되어 있어 통일된 형식으로 수정이 필요한 경우가 있습니다. 이럴 때 엑셀의 SUBSTITUTE 함수나 VBA를 활용하면 전화번호를 원하는 형태로 효율적으로 수정할 수 있습니다. 해당 함수나 VBA를 직접 다루지 못하더라도 걱정할 필요가 없습니다. ChatGPT를 활용하면 엑셀에서 데이터의 형태를 원하는 대로 어떻게 수정할 수 있는지 이 글을 통해 간단히 소개하고자 합니다.

실무예시 1: SUBSTITUTE 함수를 활용하여 전화번호 형식 수정하기

수강생의 전화번호 정보가 다양한 형태로 입력되어 있어 통일된 형식으로 수정해야 하는 상황을 가정해 봅시다. 예를 들어 일부 전화번호는 "010-1234-5678" 형태로, 다른 일부는 "01012345678" 형태로 저장되어 있습니다. 이러한 경우, SUBSTITUTE 함수를 활용하여 일관된 형식으로 전화번호를 수정할 수 있습니다.

1. 먼저 다음과 같이 ChatGPT의 입력창에 입력합니다.

1. 내가 가진 전화번호의 형태는 아래 세 가지야
01012341234
+82-10-1234-1234
010 1234 1234

2. 나는 010-1234-1234 형태로 정리하고 싶어
3. 엑셀의 기본 함수로 할 수 있는 방법을 알려줘

이때 항상 중요한 점은 엑셀을 잘 알지만 현재 작업해야 하는 엑셀 시트를 직접 만든 것이 아니라 시트 구성을 잘 모르는 다른 사람에게 부탁하는 상황을 가정하고, 최대한 상세하게 안내해야 한다는 것입니다. 조금 더 쉽게 설명한다면 내가 필요로 하는 합계 조건을 누구나 이해할 수 있도록 설명하며 ChatGPT에 입력하면 됩니다.

2. ChatGPT가 알려주는 함수를 복사해서 엑셀에 붙여넣기

그러면 다음과 같이 어떻게 접근해야 하는지를 안내한 뒤 엑셀에서 바로 활용할 수 있는 함수를 안내해 줍니다.

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", ""), "-", ""), "+82", "0"), 3) & "-" & MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", ""), "-", ""), "+82", "0"), 4, 4) & "-" & RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", ""), "-", ""), "+82", "0"), 4)

3. 오류가 나면 오류 내용을 ChatGPT에 붙여넣고 물어보기

가끔 오류가 발생할 수 있습니다. 이 경우, 오류는 아니지만 내가 원하는 값이 나오지 않는 상황입니다.

따라서 아래와 같이 다시 질문합니다.

1. 전화번호 값은 A2부터 있어.
2. 작성해준 함수를 B2에 넣으면 전화번호가 나오지 않고, '전화번-호-전화번호' 라는 값이 나오는데, 어디를 어떻게 수정해야 할까?

이에 대해 수정된 함수를 제공하며, 어떤 부분을 확인하면 문제를 해결할 수 있는지 의견도 함께 제시합니다.

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "-", ""), "+82", "0"), 3) & "-" & MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "-", ""), "+82", "0"), 4, 4) & "-" & RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "-", ""), "+82", "0"), 4)

4. ChatGPT의 안내에 따라 엑셀 수정하기

오류를 수정하면 원하는 형태로 전화번호를 수정할 수 있습니다.

실무예시 2: VBA 매크로를 사용하여 대량의 전화번호 정보 수정하기

수정해야 할 전화번호가 10개 정도라면, 위의 예시처럼 SUBSTITUTE 함수를 이용해 충분히 원하는 형태로 수정할 수 있습니다. 하지만, 수정해야 할 전화번호가 100개, 1000개인 경우는 어떨까요? 이런 상황에서는 VBA 매크로 사용이 더욱 효과적일 수 있습니다. 매크로를 사용하면 반복 작업을 자동화하여 시간을 절약하고 일관성을 유지할 수 있습니다.

1. 먼저 다음과 같이 ChatGPT의 입력창에 입력합니다.

1.. 아래는 내가 가지고 있는 전화번호 리스트야
01012341234
+82-10-1234-1234
010 1234 1234
010-1234-1234
010-1234-1234
010-1234-1234
2. 나는 전화번호를 010 뒤에 하이픈을 넣고, 다시 4자리 숫자가 나오고, 다시 뒤에 하이픈이 나오고 다시 마지막 4자리 숫자가 나오게 정리하고 싶어
3. 현재 나의 전화번호 리스트를 고려할 때 엑셀에서 어떤 함수를 사용하면 정리할 수 있을까?

2. ChatGPT가 알려주는 방법대로 VBA 매크로를 작성합니다.

ChatGPT를 통해 함수 사용법을 안내받고, 해당 함수를 엑셀에 입력했던 것처럼 VBA 매크로도 ChatGPT가 안내하는 대로 실행하면 VBA 코드의 작동 방식이나 의미를 모르더라도 누구나 활용할 수 있습니다.

먼저 VBA 매크로 사용법을 ChatGPT에서 안내받은 후에는 다음과 같은 순서로 진행합니다.

1) 엑셀에서 Alt + F11을 눌러 VBA 편집기를 엽니다.

2) 삽입 > 모듈을 선택하여 새 모듈을 생성합니다.

3) 그리고 ChatGPT가 안내한 다음의 VBA 코드를 생성한 '모듈'에 붙여 넣습니다.

Function FormatPhoneNumber(number As String) As String
Dim cleanNumber As String
cleanNumber = Replace(number, "-", "")
cleanNumber = Replace(cleanNumber, " ", "")
cleanNumber = Replace(cleanNumber, "+82", "0")

If Left(cleanNumber, 3) = "010" And Len(cleanNumber) = 11 Then
    FormatPhoneNumber = Left(cleanNumber, 3) & "-" & Mid(cleanNumber, 4, 4) & "-" & Right(cleanNumber, 4)
Else
    FormatPhoneNumber = number ' 형식이 맞지 않는 번호는 변경하지 않음
End If

End Function

4) 마지막으로 안내 받은 아래 내용대로 엑셀 창에 VBA를 실행하기 위한 함수를 입력합니다.

VBA 편집기를 닫고, 엑셀 시트에서 이 함수를 사용하려면 =FormatPhoneNumber(A1)과 같이 셀에 입력합니다. 여기서 A1은 변환하려는 전화번호가 있는 셀입니다.

VBA 매크로와 같은 엑셀의 고급 기능은 일반적으로 코딩 지식이 필요한 영역으로, 많은 이들에게 접근하기 어려운 부분일 수 있습니다. 하지만 ChatGPT의 등장으로 이러한 장벽이 크게 낮아졌습니다. ChatGPT를 활용하면 코딩 경험이 거의 없는 사람들도 복잡한 엑셀 기능을 활용할 수 있는 길이 열립니다. 마치 24시간 언제든지 도움을 줄 수 있는 친절한 멘토가 곁에 있는 것처럼, ChatGPT에게 도움을 요청하는 것은 매우 유용한 선택이 될 수 있습니다. 이를 보면 조직의 생산성 향상에 필수적인 스킬 교육의 중요성은 여전하지만 구성원들이 부족한 스킬을 AI를 통해 어떻게 향상시킬 수 있을지에 대한 고민도 필요한 시점이라고 생각합니다.

생산성을 높이기 위한 생성형AI 교육이 고민이라면,

조직의 생산성을 높이기 위해 생성형 AI 교육을 고민하고 계시다면 어디서부터 무엇을 시작하면 좋을지 상담을 받아보세요.

*상담 요청이 부담되시면 뉴스레터를 구독해 보세요. AI, 데이터 교육 관련 뉴스레터를 매주 1회 보내 드립니다.