AI Transformation

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

ChatGPT로 엑셀 자동화하기

하진규

Feb 20, 2024 • 8 min read

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

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

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

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

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

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

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

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)

오류가 나면 오류 내용을 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)

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

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

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

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

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

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

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

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

0:00

/0:27

먼저 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 기업교육 자세히 보기