엑셀로 고객 명단이나 거래처 연락처를 관리하다 보면 이름과 전화번호의 입력 형식이 제각각인 경우가 많습니다.
이름 앞뒤에 불필요한 공백이 들어가거나, 전화번호에 하이픈(-)이 빠져 있거나, 숫자와 문자가 섞여 있는 경우도 있습니다.
이런 데이터를 그대로 사용하면 고객 검색, 중복 확인, 문자 발송, 거래처 관리 과정에서 오류가 발생할 수 있습니다.
이번 글에서는 챗GPT와 엑셀 함수를 활용해 이름과 전화번호 형식을 통일하는 방법을 실제 예제로 알아보겠습니다.
1. 엑셀 이름·전화번호 형식을 통일해야 하는 이유
고객 정보를 여러 사람이 입력하거나 서로 다른 파일에서 가져오면 같은 형식으로 저장되지 않는 경우가 많습니다.
예를 들어 다음과 같은 고객 명단이 있다고 가정하겠습니다.
| 이름 | 전화번호 |
|---|---|
| 김민수 | 01012345678 |
| 이영희 | 010-2345-6789 |
| 박철수 | 010 3456 7890 |
| 최지은 | 010.4567.8901 |
| 정수진 | 01056789012 |
전화번호는 모두 휴대전화 번호이지만 입력 방식이 서로 다릅니다.
이름 역시 앞뒤 공백이나 중간의 불필요한 띄어쓰기 때문에 검색 결과가 달라질 수 있습니다.
따라서 고객 데이터를 정리할 때는 이름의 공백을 정리하고, 전화번호는 일정한 형식으로 변환하는 작업이 필요합니다.
2. 챗GPT에 엑셀 데이터 정리 요청하기
챗GPT를 이용하면 복잡한 함수를 직접 찾지 않아도 데이터에 맞는 정리 방법을 안내받을 수 있습니다.
먼저 엑셀에서 이름과 전화번호가 입력된 열을 확인합니다.
예를 들어 A열에는 이름, B열에는 전화번호가 입력되어 있다고 가정하겠습니다.
챗GPT에 다음과 같이 입력합니다.
챗GPT 입력 예시
“엑셀 A열에는 고객 이름, B열에는 전화번호가 있습니다. 이름의 불필요한 공백을 제거하고, 전화번호는 010-1234-5678 형식으로 통일하고 싶습니다. 엑셀에서 사용할 수 있는 함수와 적용 방법을 알려주세요.”
챗GPT는 이름을 정리하는 TRIM 함수와 전화번호를 정리하는 SUBSTITUTE, TEXT, IF 등의 함수를 활용하는 방법을 제안할 수 있습니다.
개인정보 보호를 위해 실제 고객 명단 전체를 입력하기보다는 가상의 예시 데이터로 함수를 먼저 만드는 것이 좋습니다.
3. TRIM 함수로 이름의 불필요한 공백 제거하기
엑셀에서 이름을 정리할 때 가장 간단하게 사용할 수 있는 함수는 TRIM입니다.
TRIM 함수는 문자 앞뒤의 일반 공백을 제거하고, 문자 사이에 연속된 일반 공백은 하나로 줄여줍니다.
A2 셀에 고객 이름이 있다면 C2 셀에 다음 수식을 입력합니다.
=TRIM(A2)
결과는 다음과 같습니다.
| 원본 이름 | 정리된 이름 |
|---|---|
| ” 김민수 “ | 김민수 |
| “이영희 “ | 이영희 |
| ” 박철수” | 박철수 |
| “최 지은” | 최 지은 |
TRIM 함수는 연속된 공백을 줄여주지만 이름 중간의 공백을 모두 없애지는 않습니다.
국내 고객 명단처럼 이름 사이의 공백을 전부 제거해야 하는 경우에는 다음 수식을 사용할 수 있습니다.
=SUBSTITUTE(TRIM(A2)," ","")
이 수식을 사용하면 ‘최 지은’도 ‘최지은’으로 정리됩니다.
다만 외국인 이름처럼 띄어쓰기가 필요한 데이터에는 중간 공백을 모두 제거하는 방식을 적용하지 않는 것이 좋습니다.
4. 전화번호에서 하이픈과 공백 제거하기
전화번호 형식을 통일하려면 먼저 숫자 사이에 들어간 하이픈, 공백, 마침표를 제거하는 것이 편리합니다.
B2 셀에 전화번호가 입력되어 있다면 D2 셀에 다음 수식을 입력합니다.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"-","")," ",""),".","")
이 수식은 세 가지 문자를 제거합니다.
- 첫 번째 SUBSTITUTE: 하이픈 제거
- 두 번째 SUBSTITUTE: 공백 제거
- 세 번째 SUBSTITUTE: 마침표 제거
실행 결과는 다음과 같습니다.
| 원본 전화번호 | 정리된 번호 |
|---|---|
| 010-1234-5678 | 01012345678 |
| 010 2345 6789 | 01023456789 |
| 010.3456.7890 | 01034567890 |
| 01045678901 | 01045678901 |
이렇게 정리하면 전화번호의 숫자만 남게 되어 다음 단계에서 동일한 형식으로 변환하기 쉬워집니다.
단, 전화번호를 숫자로 저장하면 맨 앞의 0이 사라질 수 있으므로 원본 열은 텍스트 형식으로 관리하는 것이 안전합니다.
5. 전화번호를 010-1234-5678 형식으로 자동 변환하기
전화번호에서 불필요한 문자를 제거했다면 이제 하이픈을 넣어 통일할 차례입니다.
D2 셀에 11자리 휴대전화 번호가 텍스트로 정리되어 있다면 E2 셀에 다음 수식을 입력합니다.
=IF(AND(LEN(D2)=11,LEFT(D2,3)="010"),LEFT(D2,3)&"-"&MID(D2,4,4)&"-"&RIGHT(D2,4),"확인 필요")
이 수식은 전화번호가 11자리이고 010으로 시작하는지 확인한 후 하이픈을 삽입합니다.
| 정리된 번호 | 최종 전화번호 |
|---|---|
| 01012345678 | 010-1234-5678 |
| 01023456789 | 010-2345-6789 |
| 01034567890 | 010-3456-7890 |
| 01045678901 | 010-4567-8901 |
| 0101234567 | 확인 필요 |
전화번호가 10자리이거나 010으로 시작하지 않는 경우에는 ‘확인 필요’라고 표시됩니다.
이 방법은 010으로 시작하는 11자리 국내 휴대전화 번호를 대상으로 합니다. 지역번호가 포함된 일반전화, 070 번호, 해외 전화번호 등은 별도의 규칙이 필요합니다.
또한 길이와 시작 번호만 확인하므로 실제 사용 중인 전화번호인지까지 검증하지는 않습니다.
6. 엑셀에서 이름·전화번호 정리 실습하기
실제 고객 명단을 정리하는 과정을 살펴보겠습니다.
먼저 엑셀에 다음과 같이 열을 구성합니다.
| 열 | 입력 내용 |
|---|---|
| A열 | 원본 이름 |
| B열 | 원본 전화번호 |
| C열 | 정리된 이름 |
| D열 | 숫자만 남긴 전화번호 |
| E열 | 최종 전화번호 |
2행부터 데이터를 입력한 다음 다음 수식을 적용합니다.
C2 셀: 이름 정리
=SUBSTITUTE(TRIM(A2)," ","")
D2 셀: 전화번호 특수문자 제거
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"-","")," ",""),".","")
E2 셀: 전화번호 형식 통일
=IF(AND(LEN(D2)=11,LEFT(D2,3)="010"),LEFT(D2,3)&"-"&MID(D2,4,4)&"-"&RIGHT(D2,4),"확인 필요")
각 셀에 수식을 입력한 다음 셀 오른쪽 아래의 채우기 핸들을 더블클릭하거나 아래로 드래그하면 나머지 행에도 자동 적용됩니다.
다만 원본 전화번호가 숫자로 저장되어 앞자리 0이 이미 사라졌다면 위 수식만으로 복구되지 않습니다. 이 경우 원본 자료를 확인하거나 별도의 보정 규칙을 적용해야 합니다.
7. 챗GPT로 잘못된 전화번호 찾아내기
전화번호 형식을 통일하더라도 입력 과정에서 숫자가 누락되거나 잘못 입력된 데이터가 남을 수 있습니다.
예를 들어 다음과 같은 번호입니다.
| 전화번호 | 검사 결과 |
|---|---|
| 010-1234-5678 | 정상 형식 |
| 010-123-4567 | 확인 필요 |
| 010-12345-6789 | 확인 필요 |
| 011-123-4567 | 별도 규칙 필요 |
| 010-ABCD-1234 | 확인 필요 |
챗GPT에 다음과 같이 요청할 수 있습니다.
챗GPT 입력 예시
“엑셀에 저장된 휴대전화 번호 중 010으로 시작하지 않거나 숫자가 11자리가 아닌 데이터를 자동으로 표시하는 함수를 만들어 주세요.”
숫자만 남긴 전화번호가 D2 셀에 있다면 다음 수식을 활용할 수 있습니다.
=IF(AND(LEN(D2)=11,LEFT(D2,3)="010",IFERROR(ISNUMBER(VALUE(D2)),FALSE)),"정상 형식","확인 필요")
이 수식은 번호 길이, 시작 번호, 숫자 여부를 확인합니다.
검사 결과가 ‘확인 필요’인 항목만 필터로 추출하면 잘못 입력된 전화번호를 빠르게 찾아 수정할 수 있습니다.
8. 이름과 전화번호가 중복된 고객 찾기
이름과 전화번호를 정리한 다음에는 중복된 고객이 있는지도 확인하는 것이 좋습니다.
같은 고객이 여러 번 등록되어 있으면 문자 발송이나 고객 관리 과정에서 불필요한 작업이 발생할 수 있습니다.
C열에 정리된 이름, E열에 최종 전화번호가 있다면 F2 셀에 다음 수식을 입력합니다.
=IF(OR(C2="",E2="",E2="확인 필요"),"확인 필요",IF(COUNTIFS($C$2:C2,C2,$E$2:E2,E2)>1,"중복","정상"))
이 수식은 이름과 전화번호가 모두 같은 데이터가 앞에서 이미 등장했는지 확인합니다.
첫 번째로 등록된 고객은 ‘정상’, 두 번째부터는 ‘중복’으로 표시됩니다.
다만 같은 이름과 번호가 반복되었다고 해서 반드시 삭제해야 하는 것은 아닙니다. 주문 이력이나 상담 기록처럼 중복 등록이 필요한 데이터도 있으므로 삭제 전에 확인해야 합니다.
9. 정리한 데이터를 새 엑셀 파일로 저장하기
이름과 전화번호 정리가 끝났다면 원본 데이터를 보존하면서 결과를 별도로 저장하는 것이 좋습니다.
작업 순서는 다음과 같습니다.
- C열과 E열의 정리 결과를 확인합니다.
- ‘확인 필요’로 표시된 전화번호를 점검합니다.
- 필요한 경우 중복 고객을 확인합니다.
- 정리된 이름과 전화번호를 복사합니다.
- 새 시트에 ‘값 붙여넣기’로 저장합니다.
- 새 파일을 ‘고객연락처_정리완료.xlsx’ 등의 이름으로 저장합니다.
전화번호를 새 시트에 붙여넣을 때는 해당 열을 텍스트 형식으로 설정하면 앞자리 0이 사라지는 문제를 예방할 수 있습니다.
원본 파일을 그대로 보관하면 잘못 수정한 데이터가 발견되더라도 이전 정보를 다시 확인할 수 있습니다.
10. 엑셀 이름·전화번호 정리할 때 주의할 점
이름과 전화번호를 자동으로 정리할 때는 다음 사항을 확인해야 합니다.
첫째, 이름의 모든 공백을 제거하는 방식이 항상 적절한 것은 아닙니다. 외국인 이름이나 법인명에는 공백이 필요할 수 있습니다.
둘째, 전화번호는 계산에 사용하는 숫자가 아니라 식별 정보입니다. 따라서 숫자 형식보다 텍스트 형식으로 저장하는 것이 안전합니다.
셋째, 010 휴대전화 번호와 일반전화 번호는 형식이 다릅니다. 하나의 수식으로 모든 전화번호를 무조건 변환하지 않는 것이 좋습니다.
넷째, 자동으로 정리된 전화번호가 실제로 존재하거나 해당 고객의 번호라는 의미는 아닙니다. 중요한 고객 정보는 원본과 대조해야 합니다.
다섯째, 고객 이름과 전화번호는 개인정보에 해당할 수 있으므로 외부 AI 서비스에 업로드할 때는 개인정보 처리 기준을 확인해야 합니다. 가능하면 가상 데이터로 함수를 생성한 후 실제 데이터에는 엑셀 내부에서 적용하는 것이 안전합니다.
개인정보 처리와 보호 기준은 개인정보보호위원회 공식 사이트에서 확인할 수 있습니다.
11. 자주 발생하는 오류와 해결 방법
전화번호 앞의 0이 사라지는 경우
엑셀이 전화번호를 숫자로 인식했을 가능성이 있습니다.
원본 데이터를 다시 가져올 때 해당 열을 텍스트 형식으로 지정하는 것이 좋습니다.
TRIM 함수를 사용해도 공백이 남는 경우
웹사이트에서 복사한 데이터에는 일반 공백이 아닌 특수 공백이 포함될 수 있습니다.
이 경우 다음 수식을 사용해 볼 수 있습니다.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
수식을 입력했는데 계산되지 않는 경우
셀이 텍스트 형식으로 설정되어 있거나 수식 표시 기능이 활성화되어 있는지 확인합니다.
전화번호가 ‘확인 필요’로 표시되는 경우
번호의 길이, 시작 번호, 문자 포함 여부를 확인합니다. 일반전화나 다른 휴대전화 식별번호라면 해당 번호에 맞는 별도 규칙이 필요합니다.
12. 마무리
엑셀에서 이름과 전화번호 형식을 통일하는 작업은 고객 관리의 정확도를 높이는 기본적인 데이터 정리 과정입니다.
TRIM 함수로 이름의 공백을 정리하고, SUBSTITUTE 함수로 전화번호의 불필요한 문자를 제거한 다음, IF 함수와 문자 함수를 활용하면 일정한 형식으로 변환할 수 있습니다.
여기에 챗GPT를 활용하면 데이터 구조에 맞는 수식을 빠르게 만들고, 잘못 입력된 번호나 중복 고객을 확인하는 과정까지 효율적으로 처리할 수 있습니다.
특히 고객 명단, 거래처 연락처, 회원 정보처럼 반복적으로 정리해야 하는 자료라면 한 번 만들어 둔 수식을 계속 활용할 수 있습니다.
처음에는 5~10개의 가상 데이터로 테스트하고, 결과가 정확한지 확인한 다음 전체 자료에 적용하는 것이 가장 안전한 방법입니다.
답글 남기기