엑셀(Excel) 전화번호 중간 숫자 변경(별표 처리)하기

엑셀(Excel) 전화번호 중간 숫자 변경(별표 처리)하기

 

엑셀에 입력된 전화번호의 가운데 숫자를 일괄적으로 별표(*) 처리하는 방법을 알아볼까 합니다.

 

전화번호는 개인정보로 취급되기에 간혹 번호의 중간 또는 뒷부분을 별표로 처리하는 경우가 많은데 데이터의 양이 많은 경우 이를 일일이 변경하는데 많은 시간이 소요됩니다.

 

따라서 한 번에 변경할 수 있는 방법을 알아보도록 하겠습니다.

 

우선 전화번호의 유형에 따라 적용하는 수식이 달라지므로 어떤 유형이 있는지 알아보도록 하겠습니다.

 

전화번호의 가장 뒷자리는 어떠한 경우에도 네 자리로 고정되므로 유형은 크게 앞과 중간에 위치하는 숫자의 자릿수에 의해 구분됩니다.

 

일반 전화번호의 경우 지역번호에 따라 앞자리가 두 자리 도는 세 자리로 나눠지며, 중간 자리 역시 세 자리 또는 네 자리로 나눠집니다.

휴대폰 번호의 경우 앞자리는 세 자리로 고정이나 중간 자리가 개통 시기에 따라 세 자리인 경우가 있어 세 자리 또는 네 자리로 구분됩니다.

 

이를 나타내면 전화번호의 유형은 아래와 같이 표현할 수 있습니다.

00-000-0000

00-0000-0000

000-000-0000

000-0000-0000

 

변경해야 되는 전화번호가 동일한 유형으로만 이루어진 경우, 여러 유형이 섞여있는 경우 각각 수식이 다르게 적용되므로 이를 구분하여 알아보도록 하겠습니다.

 

 

1. 전화번호가 모두 동일한 유형으로 이루어진 경우

예제

주어진 예제를 보면 번호는 모두 다르나 유형은 000-0000-0000의 형태로 구성된 것을 확인할 수 있습니다.

이렇게 동일한 유형으로 이루어진 경우 앞의 자리와 변경해야 되는 자릿수가 동일하여 아래와 같이 여러 가지 간단한 수식을 통해 결과를 만들어낼 수 있습니다.

풀이1

1번 풀이법은 LEFT, RIGHT 함수를 사용하여 앞과 뒤의 숫자와 '-'까지를 추출하며, 그 사이에 '&'로 문자를 조합할 수 있도록 한 후 '****'을 가운데 숫자 네 자리 부분에 넣는 방식입니다.

 

풀이2

2번 풀이법은 1번과 대체적으로 동일하나 가운데 별표를 추가할 때 직접 '*'을 네 번 입력하여 추가하는 것이 아닌 주어진 문자를 지정한 횟수만큼 반복하도록 하는 REPT 함수를 사용하여 '&'로 조합하는 방식입니다.

추 후 자릿수가 다른 전화번호의 변경에 REPT 함수가 쓰이게 되므로 먼저 확인해보았습니다.

 

풀이3

3번 풀이법은 REPLACE 함수를 이용하는 방법입니다.

REPLACE 함수는 REPLACE(기존 텍스트,시작 위치,자리수,변경할 텍스트)의 구조로 되어있습니다.

위의 수식을 풀어보면 REPLACE 함수를 통해 기존 전화번호 데이터의 좌측에서 5번째 자리부터 시작해서 4자리 까지를 '****' 문자로 변경하라는 의미가 됩니다.

 

유형이 모두 동일한 경우에는 위처럼 간단한 수식으로 쉽게 변경이 가능하지만 앞과 중간의 자릿수가 다른 경우 단순하게 특정 자릿수만큼 추출해서 붙이는 방식으로는 아래와 같이 제대로 된 결과를 도출할 수 없게 됩니다.

유형이 다른 경우 제대로 된 결과를 구할 수 없음

 

 

2. 전화번호 유형이 다양하게 나타나는 경우

예제

주어진 예제 안에는 다양한 유형의 전화번호 형식이 섞여있으므로 일괄적으로 몇 자리까지 추출하고 몇 자리를 변경하고 해당 값을 잇는 수식으로는 풀 수가 없습니다.

변경해야 되는 중간 자리의 자릿수도 세 자리 또는 네 자리로 나눠지고 앞자리 역시 두 자리 또는 세 자리로 구분됩니다.

 

이 예제는 다소 복잡할 수 있지만 아래와 같이 풀 수 있습니다.

풀이1

1번 풀이법을 보면 LEFT(A3,FIND("-",A3))&REPT("*",FIND("-",A3,5)-FIND("-",A3)-1)&RIGHT(A3,4)와 같이 수식을 작성할 수 있는데 이를 풀어보면 '좌측에서 문자를 추출하되 좌측 첫 번째에서 시작하여 '-' 기호가 처음 나오는 자리까지 추출하라'이며, 가운데 숫자를 변경하되 자릿수만큼 '*'를 넣기 위해 가운데 자리의 자릿수를 구하는 FIND("-",A3,5)-FIND("-",A3)-1 수식을 적용하여 좌측에서 다섯 번째 자리부터 시작하여 '-' 기호가 포함된 자리(8)에서 처음부터 시작하여 '-' 기호가 포함된 자리(4)를 빼고 각 여기에 1을 더 빼면 가운데 숫자의 자릿수 3이 나오게 됩니다.

따라서 REPT 함수의 반복 숫자 인수로 해당 수식을 통해 구한 '3'이 들어가게 되므로 별표를 세 번 반복하게 되며, 뒤의 네 자리 숫자는 RIGHT 함수를 통해 추출하는 수식을 작성하여 풀어보았습니다.

 

풀이2

2번 풀이법은 RPLACE 함수를 사용하는 것으로 1번 풀이법에서 사용했던 FIND 함수를 통해 변경할 지점의 시작점과 변경할 자릿수 지정 등을 적용하였습니다.

 

다소 복잡하게 느껴질 수 있으나 수식을 이해하고 지속적으로 풀어보면 다양한 경우에도 활용할 수 있는 방법입니다.