엑셀 함수 > 텍스트 함수 > 텍스트에서 마지막 단어 잘라내기

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

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

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

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

실습용 엑셀파일 다운로드 : 텍스트에서-마지막-단어-잘라내기.xlsx

 

 

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

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

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

[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 ① '구분 기호로 분리됨' 선택하고 ② <다음>을 누릅니다.

텍스트나누기로 마지막 단어 잘라내기 - 마법사 1단계

 

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

텍스트나누기로 마지막 단어 잘라내기 - 마법사 2단계

 

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] 범위에 붙여넣습니다.

텍스트나누기로 마지막 단어 잘라내기 - 마법사 1단계

 


관련 글

TEXTSPLIT 함수 사용법 TEXTSPLIT 함수 – 기호로 구분하여 텍스트 나누기 - 엑셀 TEXTSPLIT 함수는 기호로 구분하여 텍스트를 열 또는 행단위로 나누어 줍니다. 이 함수는 2023년 9월 현재 Microsoft 365 이상 버전에서만…

TEXTSPLIT 함수 – 기호로 구분하여 텍스트 나누기 더 보기 »

MID 함수 – 중간부터 텍스트 자르기 - 엑셀 MID 함수는 텍스트를 중간부터 지정된 개수만큼 잘라줍니다. 함수 이름 MID는 중간을 뜻하는 middle을 줄여쓴 것입니다. MID 함수는 특정 문자의…

MID 함수 – 중간부터 텍스트 자르기 더 보기 »

엑셀 SUBSTITUTE 함수 사용법 SUBSTITUTE 함수로 위치에 관계없이 텍스트 바꾸기 - 엑셀 SUBSTITUTE 함수는 텍스트의 일부를 새로운 텍스트로 바꿔 줍니다. 비슷한 기능을 하는 REPLACE 함수는 바꿀 위치를 정확히 지정해야 하는 경우에…

SUBSTITUTE 함수로 위치에 관계없이 텍스트 바꾸기 더 보기 »

COUNT 함수 사용법 COUNT함수로 갯수 구하기 COUNT, COUNTA, COUNTBLANK - 엑셀 함수 중에서 다음과 같이 COUNT로 시작하는 함수가 있는데 이름에서 유추할 수 있듯이 개수를 구해주는 함수입니다. COUNT - 숫자의 개수를…

COUNT함수로 갯수 구하기 COUNT, COUNTA, COUNTBLANK 더 보기 »

LEN 함수 사용법 LEN 함수 – 텍스트 길이 구하기 - 엑셀 LEN 함수는 텍스트의 길이(문자수)를 구해줍니다. 함수 이름 LEN은 영어의 'Length'를 줄여서 쓴 것입니다. LEN 함수는 숫자의 길이도 구할 수…

LEN 함수 – 텍스트 길이 구하기 더 보기 »

FIND 함수 – 텍스트의 위치 찾기 - 엑셀 FIND 함수는 텍스트의 위치를 찾아줍니다. 단순히 위치만 찾아주므로 함수 단독으로는 잘 사용되지 않고 LEFT, MID, RIGHT 함수 등 텍스트를 처리하는…

FIND 함수 – 텍스트의 위치 찾기 더 보기 »

INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 - 엑셀의 INDEX 함수는 테이블이나 범위에서 행과 열에 해당하는 값을 찾아줍니다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있습니다. INDEX 함수는…

INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

엑셀웍스 책 출간안내  자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

댓글 남기기

Scroll to Top