구글 스프레드 시트 16 - 다양한 전화번호 양식 통일시키기(SUBSTITUTE, IF, CONCATENATE, TEXTJOIN)

안녕하세요!

오늘은 다양한 양식으로 되어있는 전화번호를 한 양식으로 통일하는 방법에 대해서 알아보겠습니다.

 

이런 양식이 있다고 가정해봅시다.

스프레드 시트 전화번호 예제

정말(?) 다양한 양식으로 전화번호가 입력되어있는데요. 이것은 일반적인 형태인 010-1234-1234로 변경시키는 방법은 어떤게 있을까요? 일일이 손으로 하는 방법도 있지만 이 전화번호가 1000개 10000개가 된다면 손으로 하는 방법은 불가능에 가까울 것입니다.

 

그럼 어떻게 통일 시킬 수 있을까요?

 

1. 필요없는 문자는 제거하여 숫자로 된 문자만 남기자

위의 예시에서는 " ","-",".",")"과 같은 문자가 들어가 있고 이들이 불규칙적으로 들어가 있기 때문에 일단 제거하는 작업이 필요합니다. 문자열 내에서 이런 불필요한 문자를 제거하는 함수는 바로 SUBSTITUTE라는 함수입니다.

 

=SUBSTITUTE(문자열, 치환해야하는 문자, 치환하려는 문자)

스프레드 시트 SUBSTITUTE 함수

=SUBSTITUTE(A2,"-","")

이렇게 "-" 문자를 "" 아무것도 없는 것으로 치환하는 식으로 "-"를 제거 할수 있습니다. 이런 방식으로 " ","-",".",")" 모든 필요없는 문자를 제거해봅시다.

 

모든 문자를 substitute 함수 4번 활용으로 전부 제거했습니다.

제거하면 필요없는 문자가 없는 순수한 숫자로된 문자들만 남게 됩니다.

 

 

2. 10으로 시작하는 숫자에 0추가해주기

보다보면 0112341234, 0101234567 이런 숫자가 보이시죠? 앞에 0이 추가되어야 하는데요.

이건 if함수와 left 함수로 해결할 수 있습니다.

 

맨앞에 1인 전화번호에 0을 추가해주자

=if(left(E2,1)<>"0",concatenate("0",E2),E2)

# left(E2,1)<>"0": 해당 문자열의 첫번째 문자가 0이 아닌경우
# concatenate("0",E2): 문자열 맨앞에 "0" 을 추가하고
# E2: 나머지는 그냥 냅둬라

이런식으로 처리하게되면 모든 전화번호 앞에 0이 들어오게 됩니다.

 

3. 자리수가 다른 전화번호들끼리 양식 맞추기

대부분의 전화번호가 010-4자리수-4자리수 인데요.

이와중에 017-3자리수-4자리수가 섞여있다고 가정해봅시다.

(옛날 방식의 전화번호) 

 

이런경우 어떻게 해결할 수 있을까요?

바로 len 함수를 통해서 해결 할수 있습니다.

 

01012341234 -> 11자리수

0173331111 -> 10자리수

 

이런 규칙이 있는데 문자열 자리수를 계산해주는 len과 적절히 조건문을 활용하면 해결 할 수 있습니다.

len과 textjoin을 활용한 전화번호 양식 통일

=if(len(F2) = 11,
    TEXTJOIN("-",false,left(F2,3),mid(F2,4,4),right(F2,4)),
    TEXTJOIN("-",false,left(F2,3),mid(F2,4,3),right(F2,4)))
    
    
len(F2) = 11 : 문자열 길이가 11이면

TEXTJOIN("-",false,left(F2,3),mid(F2,4,4),right(F2,4)): "-" 중간중간에 넣어서 textjoin을 하는데
left(F2,3): 앞에서 3번째자리
mid(F2,4,4): 4번째에서 4번째자리
right(F2,4): 마지막에서 4번째자리

이렇게 텍스트를 합쳐라

문자열의 길이가 11이 아닐때 즉 가운데가 3자리 수 일 때

TEXTJOIN("-",false,left(F2,3),mid(F2,4,3),right(F2,4)): "-" 중간중간에 넣어서 textjoin을 하는데
left(F2,3): 앞에서 3번째자리
mid(F2,4,4): 4번째에서 3번째자리
right(F2,4): 마지막에서 4번째자리

이렇게 텍스트를 합쳐라

이렇게 되는겁니다. 이런식으로 양식이 서로안맞는 전화번호를 한 양식으로 정리 할 수 있습니다.

 

마치며

오늘은 전화번호 양식이 다를때 어떻게 처리하는지 알아봤습니다. 해당 케이스는 구글 설문지나 설문조사를 할때 많이 생기게 되는데요. 잘알아두면 편하게 전화번호를 통일 시킬 수 있을것 입니다.