문장을 .과 ?를 기준으로 분리하기

1. 문제

 

아래와 같이 대화처럼 남자, 여자가 있는 경우도 있고(A2, A3셀),

마침표나 물음표가 있으면 줄을 바꾸려고 하며,

(잠시 후)와 같이 괄호 안에 있는 문제는 제거하려고 하는 문제입니다.

 

이것이 원하는 결과입니다.

 

2. 해법 1 : TextSplit 함수

TextSplit 함수를 이용하면 간단한 줄 알았더니 TextSplit 함수를 사용하면 구분자인 마침표와 물음표가 없어지는 문제가 있고, 마지막의 마침표가 하나의 배열로 생성되기 때문에 분할하는 Text를 길이보다 하나 작은 것을 기준으로 해야 합니다.

 

가. A2셀 분리

 

(1) TextSplit의 대상인 Text를 길이보다 하나 짧은 길이로 지정하고, A2의 경우 "여자: "가 있으므로 4부터 시작

= MID(A2,4,LEN(A2)-4)

 

(2) 마침표가 있는 경우는 마침표로 분리한 후 마침표를 뒤에 붙입니다.

=TEXTSPLIT(MID(A2,4,LEN(A2)-4),".")&"."

 

(3) 물음표가 있는 경우는 물음표를 뒤에 붙입니다.

이것을 사용할 때 Substitute 함수처럼 앞의 것을 Text로 받아서 다시 한번 더 TextSplit을 해야 합니다.

따라서, 수식은

=TEXTSPLIT(TEXTSPLIT(MID(A2,4,LEN(A2)-4),".")&".","?")&"?"

가 됩니다.

 

(4) 세로로 배열하기 위해 ToCol 함수 사용

=TOCOL(TEXTSPLIT(TEXTSPLIT(MID(A2,4,LEN(A2)-4),".")&".","?")&"?")

 

라. 그러면 "?"인 경우 ".?"가 되므로 Substitute 함수를 이용해 대체

=SUBSTITUTE(TOCOL(TEXTSPLIT(TEXTSPLIT(MID(A2,4,LEN(A2)-4),".")&".","?")&"?"),".?",".")

 

그러면 결과는 아래와 같습니다.

 

나. A3셀 분리

위 수식으로 시작하는데, "남자: "와 "여자: ", "(잠시 후) "를 공란으로 바꿔야 합니다.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TOCOL(TEXTSPLIT(TEXTSPLIT(MID(A3,1,LEN(A3)-1),".")&".","?")&"?"),".?","."),"남자: ",""),"여자: ",""),"(잠시 후) ","")

 

이렇게 해도 여자의 대사가 2개인 경우는 두 번째가 공백이 있어서 Trim 함수를 다시 써야 합니다.

 

그런데 자세히 살펴보니 첫 번째 여자의 대화가 2 문장인데, 첫 번째 문장만 표시되고, 두 번째 문장은 사라지는 문제점이 있네요.

 

구두점을 유지한 채로 분리하는 방법을 모르겠네요.

 

다. B2셀과 B3셀 분리

이것을 마침표뿐이 없고, 앞의 숫자만 제거하면 되므로 간단합니다.

그렇지만 수식은 ?도 있는 수식을 그대로 사용했고,

단지 Mid 함수를 이용해 3번째부터 가져오는데, 마찬가지로 2번째 줄부터 앞에 공백이 있어서 Trim함수를 사용했습니다.

=TRIM(MID(SUBSTITUTE(TOCOL(TEXTSPLIT(TEXTSPLIT(MID(B2,1,LEN(B2)-1),".")&".","?")&"?"),".?","."),3,100))

 

 

3. 해법 2 : Subttitute 함수 사용

 

가. A2셀과 A3셀 분리

공식처럼

Substitute 함수를 이용해 구분자를 공백으로 바꾼 다음 순서대로 일정 개수를 가져오면 됩니다.

수식은 아래와 같습니다.

=MID(SUBSTITUTE(SUBSTITUTE(A2,".",REPT(" ",50)),"?",REPT(" ",50)),{0;1;2}*50+1,50)

 

그런데 여기서 특이한 점은 문장의 길이가 길다는 것이고, 

구두점을 보존해야 한다는 것입니다.

 

따라서 수식이 아래와 같이 수정돼야 합니다.

=MID(SUBSTITUTE(SUBSTITUTE(MID(A2,5,1000),".","."&REPT(" ",200)),"?","?"&REPT(" ",200)),{0;1;2;3;4;5;6;7;8}*200+1,200)

 

첫 번째 text를 가져오는 부분을 5번째부터 1000개를 가져오는 것으로 하고,

.(마침표)을 rept(" ",200)으로 50을 200으로 크게 하고, 앞에 "."&을 붙여서 마침표가 유지되도록 했으며,

?(물음표)도 마찬가지로 처리한 다음, 문장의 개수가 9개이므로 0부터 8까지 ;(중괄호)을 이용해 세로로 반환되도록(쉼표를 사용하면 가로 방향) 하는데,

마찬가지로 200개씩 가져오도록 하는 것입니다.

 

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(MID(A2,5,1000),".","."&REPT(" ",200)),"?","?"&REPT(" ",200)),{0;1;2;3;4;5;6;7;8}*200+1,200),".?","."))

그다음 ".?"가 있으므로 "."으로 대체한 다음 앞뒤 공백을 제거하면 됩니다.

 

여기서 문제는 9개인지 세봐야 안다는 것인데, Sequence 함수를 사용할 수 없으므로 자동으로 구현하기가 어렵습니다.

 

그런데 A3셀의 경우 이 수식을 이용하니 첫 번째 여자의 대화가 두 개로 잘 분리돼서 표시됩니다.

 

나. B2셀과 B3셀 분리

이것은 원문자만 제거하는 것만 다릅니다.

원문자를 제거하려면 3번째 위치부터 가져오면 됩니다.

 

=TRIM(SUBSTITUTE(MID(TRIM(MID(SUBSTITUTE(SUBSTITUTE(B2,".","."&REPT(" ",200)),"?","?"&REPT(" ",200)),{0;1;2;3}*200+1,200)),3,100),".?","."))

 위 수식과 달리 Mid(문자열, 3, 100)이 추가된 것만 다릅니다.

 

 

substitute+textsplit(해법).xlsx

0.01MB