인수 설명
01Excel 2019 이상 공식
{=TEXTJOIN ( "",, IFERROR ( MID ( 셀, NOT ( ISNUMBER ( FIND ( MID ( 셀, ROW ( INDIRECT ( "1:"&LEN ( 셀 ) ) ), 1 ), 제외문자 ) ) ) * ROW ( INDIRECT ( "1:"&LEN ( 셀 ) ) ), 1 ), "" ) )}인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
02Excel 2021 이상 공식
=LET ( chars, MID ( 셀, SEQUENCE ( LEN ( 셀 ) ), 1 ), TEXTJOIN ( "",, FILTER ( chars, ISERROR ( FIND ( chars, 제외문자 ) ), "" ) ) )인수 설명 자세히 보기
동작 원리
01Excel 2019 이상 공식
FIND 함수로 제외할 문자의 위치를 찾고, NOT 함수로 판정을 뒤집어 제외 목록에 없는 문자만 연결합니다.
Excel 2019 이상 공식의 예제 시트에서 E2 셀이 "A1_B2"를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
ROW 함수가 문자 위치 순번을 만듭니다
LEN 함수는 B2 셀의 글자 수 7을 계산하고, INDIRECT 함수와 ROW 함수는 1부터 7까지의 세로 배열을 만듭니다.
=ROW ( INDIRECT ( "1:"&LEN ( B2 ) ) )
= {1;2;3;4;5;6;7}
B2 셀의 각 문자 위치입니다. MID 함수가 문자를 한 글자씩 나눕니다
MID 함수는 위치 순번을 시작 위치로 사용해 B2 셀의 문자를 한 글자씩 분리합니다.
=MID ( B2, ROW ( INDIRECT ( "1:"&LEN ( B2 ) ) ), 1 )
= {"A";"-";"1";"_";"B";"?";"2"}
B2 셀을 한 글자씩 나눈 세로 배열입니다. NOT 함수가 제외할 위치를 뒤집습니다
FIND 함수는 C2 셀의 제외 목록에서 -와 ?를 찾습니다. ISNUMBER 함수의 결과를 NOT 함수로 뒤집은 뒤 순번을 곱해 남길 문자의 위치만 유지합니다.
=NOT ( ISNUMBER ( FIND ( MID ( B2, ROW ( INDIRECT ( "1:"&LEN ( B2 ) ) ), 1 ), C2 ) ) ) * ROW ( INDIRECT ( "1:"&LEN ( B2 ) ) )
= {1;0;3;4;5;0;7}
제외할 -와 ?의 위치는 0이 되고 나머지 위치만 남습니다. IFERROR 함수와 TEXTJOIN 함수가 결과를 합칩니다
MID 함수는 0번째 위치에서 발생한 오류를 제외하고 남길 문자를 반환합니다. IFERROR 함수가 오류를 빈 문자열로 바꾸면 TEXTJOIN 함수가 나머지 문자를 순서대로 연결합니다.
{=TEXTJOIN ( "",, IFERROR ( MID ( B2, NOT ( ISNUMBER ( FIND ( MID ( B2, ROW ( INDIRECT ( "1:"&LEN ( B2 ) ) ), 1 ), C2 ) ) ) * ROW ( INDIRECT ( "1:"&LEN ( B2 ) ) ), 1 ), "" ) )}
= "A1_B2"
-와 ?를 제외한 결과입니다. 02Excel 2021 이상 공식
LET 함수가 문자 배열을 저장하고, FILTER 함수는 제외 목록에 없는 문자만 남겨 TEXTJOIN 함수로 연결합니다.
Excel 2021 이상 공식의 예제 시트에서 E2 셀이 "A1_B2"를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
SEQUENCE 함수와 MID 함수가 문자 배열을 만듭니다
SEQUENCE 함수는 B2 셀의 글자 수만큼 위치 순번을 만들고 MID 함수는 각 위치의 문자를 분리합니다. LET 함수는 이 배열을 chars 이름에 저장합니다.
=LET ( chars, MID ( B2, SEQUENCE ( LEN ( B2 ) ), 1 ), chars )
= {"A";"-";"1";"_";"B";"?";"2"}
chars 이름에 저장된 세로 배열입니다. FIND 함수가 제외 목록에 없는 문자를 표시합니다
FIND 함수는 chars 배열의 각 문자를 C2 셀에서 찾습니다. 찾지 못한 문자는 오류가 되므로 ISERROR 함수가 남길 위치를 TRUE로 표시합니다.
=LET ( chars, MID ( B2, SEQUENCE ( LEN ( B2 ) ), 1 ), ISERROR ( FIND ( chars, C2 ) ) )
= {TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE}
-와 ?만 FALSE이고 나머지 문자는 TRUE입니다. FILTER 함수가 남길 문자만 추립니다
FILTER 함수는 TRUE인 위치의 문자만 chars 배열에서 골라 원래 순서대로 반환합니다.
=LET ( chars, MID ( B2, SEQUENCE ( LEN ( B2 ) ), 1 ), FILTER ( chars, ISERROR ( FIND ( chars, C2 ) ), "" ) )
= {"A";"1";"_";"B";"2"}
제외 목록에 없는 다섯 문자입니다. TEXTJOIN 함수가 남은 문자를 연결합니다
TEXTJOIN 함수는 FILTER 함수가 반환한 다섯 문자를 빈 구분자로 이어 최종 문자열을 만듭니다.
=LET ( chars, MID ( B2, SEQUENCE ( LEN ( B2 ) ), 1 ), TEXTJOIN ( "",, FILTER ( chars, ISERROR ( FIND ( chars, C2 ) ), "" ) ) )
= "A1_B2"
-와 ?를 제외한 결과입니다. 