메뉴
실무 위키›응용 공식›주민등록번호와 사업자등록번호가 섞인 목록에서 번호 유형 구분하기

주민등록번호와 사업자등록번호가 섞인 목록에서 번호 유형 구분하기

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

목록에 섞여 있는 10자리·13자리 번호를 자릿수로 나눠 번호 유형을 표시합니다.

예제파일 다운로드
주민등록번호와 사업자등록번호가 섞인 목록에서 번호 유형 구분하기 예제 미리보기
예제 미리보기 크게 보기

인수 설명

0110자리와 13자리 번호 형식 구분하기

=IFERROR ( INDEX ( 유형 목록, MATCH ( LEN ( SUBSTITUTE ( SUBSTITUTE ( 원본 번호, "-", "" ), " ", "" ) ), 자릿수 목록, 0 ) ), "확인 필요" )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
원본 번호필수자릿수로 형식을 나눌 번호입니다. 13자리 번호에는 법인등록번호와 외국인등록번호도 있으므로 결과는 자릿수에 따른 형식일 뿐 실제 번호 유형이나 유효성은 확인하지 않습니다. 앞자리 0이 필요한 번호는 처음부터 텍스트로 입력합니다.
자릿수 목록필수분류 기준이 되는 자릿수 목록입니다. 숫자로 입력합니다. 텍스트로 적은 자릿수는 찾지 못해 그 자릿수의 번호가 모두 확인 필요로 표시됩니다.
유형 목록필수자릿수 목록과 같은 순서로 적은 형식 이름입니다.
"확인 필요"필수번호가 10자리나 13자리가 아닐 때 반환할 표시입니다.
B2=IFERROR(INDEX($F$2:$F$3,MATCH(LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")),$E$2:$E$3,0)),"확인 필요")
A
B
C
D
E
F
G
H
1
원본 번호
번호 유형
숫자 구성
자릿수 기준
유형 이름
건수
2
12A-45-67890
사업자번호 형식
글자 섞임
13
주민번호 형식
3
3
991332-1234567
주민번호 형식
숫자만
10
사업자번호 형식
3
4
123-45-67890
사업자번호 형식
숫자만
5
4108176543
사업자번호 형식
숫자만
6
881332 2345678
주민번호 형식
숫자만
7
123-45-6789
확인 필요
숫자만
8
110111-1234567
주민번호 형식
숫자만
9
10자리는 사업자번호 형식, 13자리는 주민번호 형식으로 표시합니다. 12A-45-67890은 문자가 섞여도 10글자라 사업자번호 형식으로 표시되고, 110111-1234567은 법인등록번호지만 13자리라 주민번호 형식으로 표시됩니다. 실제 번호 유형이나 유효성은 확인하지 않습니다.

02번호에 숫자만 있는지 확인하기

=IF ( ISNUMBER ( -SUBSTITUTE ( SUBSTITUTE ( 원본 번호, "-", "" ), " ", "" ) ), "숫자만", "글자 섞임" )
인수 설명 자세히 보기
인수구분설명
원본 번호필수숫자 구성을 확인할 번호입니다. 하이픈과 공백만 지운 뒤 숫자로 바꿀 수 있는지 검사하므로, 마침표(.)나 E처럼 엑셀이 숫자의 일부로 읽는 글자가 섞이면 숫자만으로 표시될 수 있습니다. 예) 991332.1234567 → 숫자만
C2=IF(ISNUMBER(-SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")),"숫자만","글자 섞임")
A
B
C
D
E
F
G
H
1
원본 번호
번호 유형
숫자 구성
자릿수 기준
유형 이름
건수
2
12A-45-67890
사업자번호 형식
글자 섞임
13
주민번호 형식
3
3
991332-1234567
주민번호 형식
숫자만
10
사업자번호 형식
3
4
123-45-67890
사업자번호 형식
숫자만
5
4108176543
사업자번호 형식
숫자만
6
881332 2345678
주민번호 형식
숫자만
7
123-45-6789
확인 필요
숫자만
8
110111-1234567
주민번호 형식
숫자만
9
문자 A가 섞인 첫 번호는 글자 섞임으로 표시됩니다. 9자리인 123-45-6789는 자릿수 조건에는 맞지 않지만 숫자로만 구성되어 숫자만으로 표시됩니다.

03자릿수별 번호 건수 세기

=SUMPRODUCT ( -- ( LEN ( SUBSTITUTE ( SUBSTITUTE ( 번호 범위, "-", "" ), " ", "" ) ) = 자릿수 ) )
인수 설명 자세히 보기
인수구분설명
자릿수필수건수를 셀 자릿수입니다. 숫자로 입력합니다. 텍스트로 적은 자릿수는 번호 길이와 같다고 판정되지 않아 건수가 0으로 표시됩니다.
번호 범위필수집계할 번호가 입력된 범위입니다. 예제의 110111-1234567은 법인등록번호지만 13자리라 같은 그룹에 포함됩니다. 실제 번호 유형이나 유효성은 확인하지 않습니다.
G2=SUMPRODUCT(--(LEN(SUBSTITUTE(SUBSTITUTE($A$2:$A$8,"-","")," ",""))=E2))
A
B
C
D
E
F
G
H
1
원본 번호
번호 유형
숫자 구성
자릿수 기준
유형 이름
건수
2
12A-45-67890
사업자번호 형식
글자 섞임
13
주민번호 형식
3
3
991332-1234567
주민번호 형식
숫자만
10
사업자번호 형식
3
4
123-45-67890
사업자번호 형식
숫자만
5
4108176543
사업자번호 형식
숫자만
6
881332 2345678
주민번호 형식
숫자만
7
123-45-6789
확인 필요
숫자만
8
110111-1234567
주민번호 형식
숫자만
9
13자리 3건에는 법인등록번호 예시가, 10자리 3건에는 문자가 섞인 번호가 함께 들어 있습니다. 자릿수만 세므로 실제 번호 유형이나 유효성은 확인하지 않습니다.
이 공식이 사용하는 함수
LEN 함수 SUBSTITUTE 함수 IFERROR 함수 INDEX 함수 MATCH 함수 IF 함수 ISNUMBER 함수 SUMPRODUCT 함수

동작 원리

0110자리와 13자리 번호 형식 구분하기

SUBSTITUTE 함수와 LEN 함수로 구분 기호를 뺀 글자 수를 구하고, MATCH 함수가 자릿수 목록에서 찾은 위치로 INDEX 함수가 형식 이름을 가져옵니다.

예제 시트에서 첫 번호의 형식을 표시하는 B2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

구분 기호를 지우고 자릿수를 셉니다

첫 번호에서 하이픈을 지우면 12A4567890이 남고 글자 수는 10입니다. 이 단계는 숫자 구성까지 확인하지 않습니다.

=LEN ( SUBSTITUTE ( SUBSTITUTE ( A2, "-", "" ), " ", "" ) )
= 10
2

자릿수 목록에서 같은 값의 위치를 찾습니다

자릿수 목록에서 10은 두 번째에 있으므로 MATCH 함수가 2를 반환합니다.

=MATCH ( 10, $E$2:$E$3, 0 )
= 2
3

같은 위치의 형식 이름을 반환합니다

유형 목록의 두 번째 값은 사업자번호 형식입니다. 문자 구성과 실제 발급 여부는 이 결과만으로 확인할 수 없습니다.

=INDEX ( $F$2:$F$3, 2 )
= 사업자번호 형식

02번호에 숫자만 있는지 확인하기

SUBSTITUTE 함수로 하이픈과 공백을 지운 값 앞에 빼기 기호(-)를 붙여 숫자로 바꿔 보고, ISNUMBER 함수의 판정에 따라 IF 함수가 숫자만 또는 글자 섞임을 표시합니다.

예제 시트에서 문자 A가 섞인 첫 번호를 확인하는 C2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

번호에서 구분 기호만 지웁니다

하이픈과 공백만 지우므로 문자 A는 그대로 남습니다.

=SUBSTITUTE ( SUBSTITUTE ( A2, "-", "" ), " ", "" )
= 12A4567890
2

숫자로 바꿀 수 있는지 확인합니다

문자 A가 들어 있어 숫자로 바꿀 수 없으므로 ISNUMBER 함수가 FALSE를 반환합니다.

=ISNUMBER ( -"12A4567890" )
= FALSE
3

문자가 섞였다는 표시를 반환합니다

숫자인지 확인한 결과가 FALSE이므로 글자 섞임을 반환합니다.

=IF ( FALSE, "숫자만", "글자 섞임" )
= 글자 섞임

03자릿수별 번호 건수 세기

SUBSTITUTE 함수와 LEN 함수로 번호마다 구분 기호를 뺀 글자 수를 구하고, 찾을 자릿수와 같은 번호만 1로 바꿔 SUMPRODUCT 함수로 더합니다.

예제 시트에서 13자리 번호 건수를 구하는 G2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

번호마다 구분 기호를 뺀 길이를 구합니다

일곱 번호의 글자 수는 차례대로 10, 13, 10, 10, 13, 9, 13입니다.

=LEN ( SUBSTITUTE ( SUBSTITUTE ( $A$2:$A$8, "-", "" ), " ", "" ) )
= {10;13;10;10;13;9;13}
2

13자리인 번호만 1로 바꿉니다

각 길이를 기준값 13과 비교하면 둘째·다섯째·일곱째 번호만 TRUE가 되고, 앞의 --(이중 마이너스)가 TRUE를 1로, FALSE를 0으로 바꿉니다.

=-- ( {10;13;10;10;13;9;13} = E2 )
= {0;1;0;0;1;0;1}
3

조건을 만족한 번호의 개수를 더합니다

1로 바뀐 세 값을 SUMPRODUCT 함수로 더하면 13자리 번호는 3건입니다.

=SUMPRODUCT ( {0;1;0;0;1;0;1} )
= 3
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료