엑셀 휴대폰번호 010 사라질 때: 앞자리 0 살리고 하이픈까지 맞추는 초보 정리법

엑셀 휴대폰번호 앞자리 0 사라짐 문제는 생각보다 자주 만납니다. 고객명단을 받아서 열어 봤더니 010-1234-5678이어야 할 번호가 1012345678처럼 보이거나, 어떤 행은 하이픈이 있고 어떤 행은 없는 식으로 섞여 있죠.

처음 보면 엑셀이 숫자를 잘못 바꾼 것처럼 느껴지지만, 원인은 단순합니다. 엑셀은 셀에 입력된 값을 숫자로 판단하면 맨 앞의 0을 필요 없는 숫자로 보고 지워 버립니다. 휴대폰번호는 계산할 숫자가 아니라 ‘문자처럼 다뤄야 하는 코드’라고 생각하면 훨씬 편합니다.

Q. 왜 010을 입력했는데 10으로 시작하나요?

엑셀에서 01012345678을 그냥 입력하면 숫자로 인식될 수 있습니다. 숫자에서 맨 앞 0은 값에 영향을 주지 않기 때문에, 엑셀은 이것을 1012345678로 저장합니다.

예를 들어 00123과 123은 계산 결과가 같죠. 엑셀 입장에서는 앞의 0을 굳이 보관할 이유가 없습니다. 하지만 휴대폰번호, 사번, 상품코드처럼 앞자리 0 자체가 의미가 있는 데이터는 이야기가 달라집니다.

이번 예제는 아래처럼 고객 접수 명단을 정리하는 상황으로 보겠습니다. 표는 A1:F7 범위에 있고, 실제 데이터는 2행부터 7행까지 들어 있습니다.

여기서 C열은 원본입니다. 직접 입력했거나, 설문 파일에서 내려받았거나, 다른 시스템에서 복사해 온 값이라고 보면 됩니다. 우리가 만들 결과는 D열 발송용 번호입니다. 모든 번호를 010-1234-5678 형식으로 맞춰 둘 겁니다.

Q. 셀 서식만 바꾸면 해결 아닌가요?

간단히 보기만 맞출 때는 셀 서식을 사용할 수 있습니다. C열을 선택한 뒤 Ctrl + 1을 누르고, 표시 형식 > 사용자 지정에서 형식을 000-0000-0000으로 지정하면 숫자가 휴대폰번호처럼 보입니다.

하지만 실무에서는 여기서 한 번 더 생각해야 합니다. 셀 서식은 주로 ‘보이는 모양’을 바꾸는 기능입니다. 문자 발송 업체에 업로드하거나, 다른 파일과 비교하거나, VLOOKUP으로 찾을 때는 실제 값이 예상과 다르면 또 문제가 생길 수 있습니다.

그래서 저는 원본 C열은 그대로 두고, D열에 수식으로 정리된 번호를 따로 만드는 방식을 추천합니다. 원본을 건드리지 않으니 실수했을 때 되돌리기도 쉽습니다.

Q. D2 셀에 어떤 수식을 넣으면 되나요?

D2 셀을 클릭한 뒤 아래 수식을 입력합니다. 이 수식은 C2에 있는 번호에서 하이픈과 띄어쓰기를 없앤 다음, 다시 000-0000-0000 모양으로 맞춰 줍니다.

=IF(C2="","",TEXT(SUBSTITUTE(SUBSTITUTE(C2,"-","")," ",""),"000-0000-0000"))

입력한 뒤 D2 셀 오른쪽 아래 작은 점을 잡고 D7까지 내려 복사합니다. 그러면 D2:D7 범위에 발송용 번호가 만들어집니다.

수식이 낯설다면 이렇게 나눠서 보면 됩니다. SUBSTITUTE(C2,"-","")는 하이픈을 없애는 부분입니다. 그다음 SUBSTITUTE(...," ","")는 중간에 들어간 띄어쓰기를 없애는 부분입니다. 마지막으로 TEXT(...,"000-0000-0000")가 010-1234-5678 모양으로 바꿔 줍니다.

앞의 IF(C2="","",...)는 C2가 빈칸일 때 D2도 빈칸으로 두기 위한 장치입니다. 이 부분이 없으면 빈칸 행에 이상한 번호처럼 보이는 값이 생길 수 있어서, 실무 파일에서는 넣어 두는 편이 안전합니다.

Q. 010이 이미 사라진 번호도 다시 살릴 수 있나요?

대부분의 휴대폰번호라면 가능합니다. 예를 들어 C2에 1012345678처럼 10자리 숫자로 들어가 있어도, 위 수식의 000-0000-0000 형식이 앞에 0을 채워서 010-1234-5678로 보여 줍니다.

다만 주의할 점이 있습니다. 원래 번호가 010인지, 011인지, 다른 번호였는지 구분이 필요한 오래된 데이터라면 앞자리 0만 보고 무조건 복구할 수는 없습니다. 요즘 고객 휴대폰번호 정리처럼 대부분 010으로 시작하는 명단에서는 잘 맞지만, 오래된 회원 DB라면 원본 시스템과 한 번 대조하는 것이 좋습니다.

Q. 같은 휴대폰번호가 두 번 들어온 것도 찾고 싶어요

정리된 번호가 D열에 만들어졌다면 중복 확인은 훨씬 쉬워집니다. E2 셀에 아래 수식을 입력하고 E7까지 복사해 보세요.

=IF(COUNTIF($D$2:$D$7,D2)>1,"중복","")

여기서 COUNTIF는 조건에 맞는 셀 개수를 세는 함수입니다. $D$2:$D$7은 검사할 전체 번호 범위이고, D2는 현재 행의 번호입니다.

범위에 달러 표시가 붙어 있는 이유도 중요합니다. 수식을 아래로 복사해도 검사 범위가 D2:D7로 고정되어야 하기 때문입니다. 만약 달러 표시가 없으면 아래 행으로 내려갈수록 검사 범위가 밀려서 중복 결과가 이상해질 수 있습니다.

Q. 번호로 고객명을 찾을 때 VLOOKUP이 안 되는 이유는 뭔가요?

휴대폰번호로 고객명을 찾을 때도 원인은 비슷합니다. 한쪽 파일은 010-1234-5678이고, 다른 파일은 1012345678이면 눈으로는 같은 사람 같아도 엑셀은 다른 값으로 봅니다.

찾기용 번호를 J2 셀에 입력하고, K2 셀에 고객명을 표시한다고 해 보겠습니다. 이미 D열에 정리된 발송용 번호가 있고, B열에 고객명이 있으니 XLOOKUP을 쓸 수 있는 버전이라면 아래처럼 입력합니다.

=XLOOKUP(TEXT(SUBSTITUTE(SUBSTITUTE(J2,"-","")," ",""),"000-0000-0000"),$D$2:$D$7,$B$2:$B$7,"없음")

이 수식은 J2에 010-1234-5678로 입력하든, 01012345678로 입력하든, 1012345678로 입력하든 먼저 같은 형식으로 정리한 뒤 D열에서 찾습니다. 그래서 원본 모양이 조금 달라도 조회 성공률이 올라갑니다.

XLOOKUP이 없는 버전이라면 VLOOKUP을 쓰기 위해 번호가 맨 왼쪽에 있는 작은 조회표를 따로 만들어도 됩니다. 예를 들어 H열에 발송용 번호, I열에 고객명을 복사해 둔 뒤 K2에 아래처럼 입력합니다.

=IFERROR(VLOOKUP(TEXT(SUBSTITUTE(SUBSTITUTE(J2,"-","")," ",""),"000-0000-0000"),$H$2:$I$7,2,FALSE),"없음")

FALSE는 정확히 같은 값만 찾겠다는 뜻입니다. 휴대폰번호처럼 한 글자라도 다르면 다른 사람일 수 있는 데이터는 반드시 정확히 일치로 찾아야 합니다.

Q. 수식이 안 맞을 때 어디부터 확인하면 되나요?

먼저 C열에 휴대폰번호 말고 다른 문자가 섞였는지 확인합니다. 예를 들어 “010-1234-5678 / 부재중”처럼 메모가 같이 들어 있으면 위 수식만으로는 정리하기 어렵습니다. 이럴 때는 번호와 메모를 열로 분리해서 관리하는 것이 좋습니다.

두 번째로 D열 결과가 전부 같은 형식인지 봅니다. 어떤 행은 010-1234-5678이고 어떤 행은 01012345678이면, 중복 체크나 조회에서 또 틀어질 수 있습니다.

세 번째로 수식을 복사한 범위를 확인합니다. 예제에서는 데이터가 2행부터 7행까지라서 D2:D7, E2:E7까지만 복사했습니다. 내 파일이 2행부터 500행까지라면 수식도 D500, E500까지 내려가야 합니다.

Q. 처음부터 010이 안 사라지게 입력하려면요?

새로 입력하는 파일이라면 C열 전체를 먼저 선택하고 Ctrl + 1을 누른 뒤, 표시 형식 > 텍스트로 바꿔 두세요. 그다음 01012345678을 입력하면 앞자리 0이 유지됩니다.

또는 입력할 때 맨 앞에 작은따옴표를 붙여 '01012345678처럼 입력할 수도 있습니다. 셀에는 작은따옴표가 보이지 않고 01012345678만 보입니다. 다만 많은 건을 입력할 때는 열 전체를 텍스트로 지정하는 편이 덜 헷갈립니다.

이미 받은 파일을 정리하는 상황이라면 원본 열을 억지로 고치기보다, 이번 예제처럼 D열에 정리번호를 만들어 쓰는 방식이 안전합니다. 고객명단, 문자발송 명단, 상담 접수표, 이벤트 신청자 명단처럼 휴대폰번호를 기준으로 중복 확인이나 조회를 해야 할 때 특히 유용합니다.

이럴 때 이렇게 쓰면 된다

단순히 화면에 예쁘게 보이기만 하면 된다면 셀 서식 사용자 지정을 쓰면 됩니다. 하지만 문자 발송, 중복 제거, 고객 조회, 다른 파일과 비교까지 해야 한다면 TEXT 수식으로 정리번호 열을 따로 만드는 방식이 더 안정적입니다.

원본은 C열에 그대로 두고, D열에 발송용 번호를 만들고, E열에서 COUNTIF로 중복을 확인하세요. 번호로 고객명을 찾아야 할 때도 J2의 검색 번호를 먼저 같은 형식으로 바꾼 뒤 XLOOKUP이나 VLOOKUP으로 찾으면 됩니다. 휴대폰번호는 숫자가 아니라 코드라는 점만 기억하면, 010 사라짐 문제는 꽤 깔끔하게 정리됩니다.