특정 문자 사이의 문자열 추출하기 - Index, TextSplit

1. 문제

아래와 같은 문자열에서 괄호 사이의 문자열을 추출하려고 합니다.

 

특정문자사이 문자열 추출(문제).xlsx

0.01MB

 

 

2. TextSplit, Index, TextBefore 함수의 구문

 

가. TextSplit 함수

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

① text : 문자열 주소

② col_delimiter : 가로로(열) 구분할 문자열

③ row_delimiter : 세로로(행) 구분할 문자열

④ ignore_empty : 구분 문자열이 연속될 경우 빈 배열이 반환되는데, 이를 무시할지 여부로, 무시하면(True) 빈 배열을 없애고, 무시하지 않으면(False) 빈 배열이 반환됩니다. 기본 값을 False입니다.

 

=TEXTSPLIT(A2,"(",,TRUE)일 경우 빈 배열을 무시하므로 두 번째 빈 셀이 표시되지 않으며,

 

=TEXTSPLIT(A2,"(",,FALSE) 또는 =TEXTSPLIT(A2,"(",)일 경우

빈 배열을 무시하지 않으므로 두 번째 빈 셀이 표시됩니다.

 

⑤ match_mode : 대/소문자 구분 여부로 기본값은 구분하는 것(0, 영)입니다. 따라서 구분하지 않으려면 1로 지정해야 합니다.

 

⑥ pad_with : 행/열의 개수를 맞출 때 사용할 문자로서 기본값은 #N/A입니다.

B2셀에 =TEXTSPLIT(A2,"(","*")라고 수식을 입력하면

"("는 열 구분 문자열이고, "*"는 행 구분 문자열이므로

 

첫 번째 행에는 '전과목 1등급','','HS반)이 반환되고,

두 번째 행에는 '한국사, 제2외국어제외'가 반환되므로

첫 번째 행은 3개 열이고, 두 번째 행은 1개 열이므로

개수가 맞지 않는 열에는 #N/A가 채워지는 것입니다.

 

만약 pad_with를 ""으로 입력해서 =TEXTSPLIT(A2,"(","*",,,"")라고 하면

차이나는 열이 공백으로 채워집니다.

 

나. Index 함수

배열형인 경우

INDEX(array, row_num, [column_num])

는 배열에서 행 수와 열 수가 교차하는 셀의 값을 반환합니다.

column_num은 생략가능합니다.

 

참조형인 경우의 구문은

INDEX(reference, row_num, [column_num], [area_num])

로서 area_num이 있다는 것이 다르며, 따라서, reference를 여러 개의 셀 범위를 참조하도록 지정할 수 있는데,

생략하면 배열형이나 같아집니다.

 

=INDEX(A2:A5,2,1) 또는 =INDEX(A2:A5,2)라고 입력하면 2행의 데이터인 A3셀의 값을 반환합니다.

 

다. TextBefore 함수

=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

로 복잡한데,

 

① text : 문자열 주소

② delimiter : 구분할 문자열

③ instance_num : 구분 문자열이 여러 개 있을 경우 순번입니다.

④ match_mode : 대/소문자 구분 여부로 기본값은 구분하는 것(0, 영)입니다. 따라서 구분하지 않으려면 1로 지정해야 합니다.

⑤ match_end : 문자열의 끝을 구분 문자열로 처리할지 여부로,

0이면 정확히 일치하는 구분 문자열의 전까지만 반환하므로 구분 문자열이 없을 경우 #N/A를 반환하는 데 반해

예) =TEXTBEFORE(A2,"{",,,0) 또는 =TEXTBEFORE(A2,"{")

 

1은 정확히 일치하는 구분 문자열이 없더라도 문자열의 끝까지를 결괏값으로 반환합니다.

예) =TEXTBEFORE(A2,"{",,,1)

 

⑥ if_not_found    일치하는 구분 문자열이 없을 경우 반환되는 값으로 기본값은 #N/A입니다.

 

만약 if_not_found값으로 a2셀을 지정하면 

 

일치하는 값이 없을 때 A2셀 값을 반환하므로

 

=TEXTBEFORE(A2,"{",,,1)이나 결과가 같습니다.

 

3. 해법 1

먼저 A2셀에서 "(("를 "("로 수정합니다.

 

TextSplit 함수를 이용해서 "(" 문자열을 기준으로 분리한 후,

Index함수를 이용해 배열에서 2번째 문자열을 추출하고,

다시 TextBefore 함수로 ")"전까지의 문자열을 추출하면 되므로

 

수식은

=TEXTBEFORE(INDEX(TEXTSPLIT(A2,"("),2),")")가 됩니다.

 

index 함수의 행 수를 2로 지정했는데,

열 수를 2로 지정해도 값은 같습니다.

 

=TEXTSPLIT(A2,"(")의 결괏값이 열 2개이므로 열 수를 2로 지정하는 것이 원칙입니다.

 

 

 

4. 해법 2

구분 문자열을 배열을 이용해 {"(",")"}라고 해서 "("과 ")"를 지정할 수 있으며,

이렇게 TextSplit 함수의 수식을 아래와 같이 적용하면

=TEXTSPLIT(A2,{"(",")"})

 

(와 ) 사이의 문자열이 배열의 두 번째 값이므로

Index함수를 이용해 배열의 2번째 값을 가져오면 됩니다.

 

이제 B2셀의 채우기 핸들을 더블 클릭하면

A4셀까지 수식이 복사되고, 원하는 값이 모두 추출됩니다.

 

 

5. 해법 3

TextAfter 함수에 대해서는 설명하지 않았지만, After가 붙어 있듯이 구분 문자열 다음의 문자열을 반환하는 함수입니다.

따라서, TextAfter함수로 ( 다음의 문자열을 추출한 후 TextBefore함수로 ) 전의 문자열을 추출하면 됩니다.

 

=TEXTBEFORE(TEXTAFTER(A2,"("),")")

 

특정문자사이 문자열 추출(완성).xlsx

0.01MB