엑셀 95강] 010-0000-0000 형태로 입력된 폰 번호에서 하이픈만 빼기

 

엑셀 수업 시간에 종종 받는 질문 중 하나를 소개하겠습니다.

 

 

"010-0000-0000 형태로 입력된 폰 번호에서 -(하이픈)을 빼고 싶습니다.
폰 번호가 수백 또는 수천 건이 되어서 수작업으로 할 수 없어 엑셀로 처리를 하고 싶습니다"

 

실습 파일 다운로드

95강-폰번호에서하이픈빼기.xlsx

다운로드

 

 

일반적으로 '찾아 바꾸기' 기능을 사용하면 편리합니다.

연락처 범위를 선택하고 [홈]-[찾기 및 선택]-[바꾸기] 메뉴를 실행합니다.

찾을 내용 입력창에 "-"을 입력하고 바꿀 내용에는 아무것도 입력하지 않습니다.

그런 후 [모두 바꾸기]를 누르면 됩니다.

 

 

하지만 결과가 조금 이상합니다.

폰 번호에서 하이픈을 없애긴 했는데 앞 자리 '0'이 사라졌네요.

왜냐하면 하이픈이 제거되면서 연락처 데이터 형식이 숫자로 변했기 때문입니다.

숫자 앞자리 0은 유효하지 않는 숫자죠.

셀에서 01234를 입력해보세요. 1234만 입력이 됩니다.

그럼 앞자리 0이 표시되면서 하이픈을 없애려면 어떻게 해야 할까요?

 

 

첫 번째 표시 형식을 변경합니다.

연락처에 블록을 지정하고 단축키 <Ctrl + 1>을 누르세요. 셀 서식이 실행됩니다.

'사용자 지정'을 선택하고 '형식' 입력창에 "000-0000-0000"이라고 입력합니다.

 

 

간단하게 처리가 되었습니다.

하지만 표시 형식으로 변경한 연락처는 실제 0이 존재하지 않습니다.

단지 보이기만 할 뿐이죠.

실제 0이 표시되도록 하려면 함수를 사용해야 합니다.

두 번째 방법을 참고하세요.

 

 

두 번째 Substitute 함수를 사용합니다.

열을 하나 추가하고 =SUBSTITUTE(C2,"-","")라고 작성합니다.

첫 번째 인수는 학생 연락처, 두 번째 인수는 바꿀 "-", 세 번째 인수는 ""(공백)을 입력합니다.

네 번째 인수는 몇 번째 위치에 글자를 바꿀건지 위치를 입력하는 인수입니다.

생략가능 합니다.

 

 

어떤가요? 앞 자리 0이 유지된 채로 변경되었죠?

이유는 Substitute 함수가 텍스트 범주에 속하는 함수이기 때문입니다.

당연히 결과도 텍스트 형식이 되겠죠?

수식을 그대로 두지 말고 그 범위를 복사한 후 '선택하여 붙여넣기', '값'을 해줍니다.

 

 

짤막한 강좌 한정희 강사였습니다. 즐거운 하루 보내세요~