텍스트에서 마지막 단어 잘라내기

엑셀을 이용하여 자료를 다루다 보면 다음과 같이 하이픈으로 구분된 텍스트에서 가장 마지막에 있는 단어를 잘라내야 할 경우가 있습니다.

문제는 길이도 다르고 위치도 달라서 잘라내기가 쉽지 않습니다. MID, FIND, SUBSTITUTE, LEN 함수 등 여러 함수를 조합하여 해결해 보겠습니다.

 

 

구분 문자 위치를 찾아서 잘라내기

※ 수식을 읽기 쉽도록 수식입력줄에서 줄을 바꾸고 들여쓰기 함(수식은 정상 작동함)

[C5] 셀에 다음 수식을 입력하면 하이픈으로 구분된 텍스트에서 마지막 단어를 잘라냅니다.

=MID(B5,FIND("@",SUBSTITUTE(B5,"-","@",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))+1,LEN(B5))

 

이해를 위해 최종 결과부터 거꾸로 수식을 설명하겠습니다.

[B5] 셀에 'PS-30-31542-AX'가 입력되어 있을 때 마지막 'AX'을 잘라내려면

'AX'는 세 번째 하이픈(12번째에 위치) 바로 뒤에 있으므로 MID 함수로 13번째부터 잘라내면 됩니다.

=MID(B5,13,LEN(B5)) => 'AX'

결국 세 번째 하이픈의 위치 12를 알아내는 것이 이 수식의 핵심입니다.

SUBSTITUTE 함수로 세 번째의 하이픈을 @로 바꾸고(PS-30-31542-AX →  PS-30-31542@AX)
FIND 함수로 @의 위치를 찾으면 12가 나옵니다.

=FIND("@","PS-30-31542@AX") => 12
※ 앞에 있는 2개의 하이픈과 구별하기 위해 3번째 하이픈을 텍스트에 없는 값 @(주로 #,^ 등 특수문자를 많이 사용함)로 바꿉니다.

SUBSTITUTE 함수로 세번째 하이픈을 @로 바꾸려면  마지막 인수 값 3을 알아야 합니다.
=SUBSTITUTE(B5,"-","@",3) => 'PS-30-31542@AX'

결국 3은 텍스트에 포함된 전체 하이픈 개수이므로 다음과 같이 구합니다(전체 길이에서 하이픈을 제외한 길이를 뺌).
=LEN(B14)-LEN(SUBSTITUTE(B14,"-","")) => 3

 

 

텍스트 나누기 후 가져오기

앞에서 사용한 수식은 한 번에 결과를 가져오지만 수식이 복잡하고 중간 과정이 보이지 않아서 이해가 어렵습니다.
다음과 같이 풀어서 해결하면 중간 과정도 볼 수 있고 수식도 간단하여 누구나 쉽게 이해할 수 있습니다.

'텍스트 나누기'기능을 이용하여 C열부터 H열에 텍스트를 나누어 보겠습니다.

01 ① 텍스트 나누기를 할 C열의 28행부터 33행까지 선택하고 ② 리본 메뉴 [데이터] 탭 → [데이터 도구] 그룹 → [텍스트 나누기]를 누릅니다.

 

02 ① '구분 기호로 분리됨' 선택하고 ② <다음>을 누릅니다.

 

03 ① 구분 기호에서 [기타]를 선택하고 입력란에 '-'(하이픈) 입력하고 ② <마침>을 누릅니다.

 

04 다음 그림과 같이 텍스트가 나눠지면 [I28] 셀에 다음 수식을 입력합니다.

=INDEX(C28:H28,1,COUNTA(C28:H28))

COUNTA 함수는 [C28:H28] 범위에서 텍스트가 입력된 셀의 개수 4를 반환하므로 위 식은 =INDEX(C28:H28,1,4)와 같고 INDEX 함수는 [C28:H28] 범위에서 4번째 열에 있는 값 'AX'를 가져옵니다.

 

05 [I28] 셀을 복사하여 [I29:I33] 범위에 붙여넣습니다.

 

---

관련 글