엑셀(EXCEL) 텍스트를 뒤에서부터 검색하는 함수(글자를 꺼꾸로 세거나 파일의 확장자 지우기)

문장 안에 글자를 찾는 함수는 Find와 Search가 있습니다.

(Find, Search 함수로 글자 안에서 글자를 검색)

하지만 두가지 이상을 검색할 수가 없다는 것인데 불규칙한 길이의 텍스트라면 어렵습니다.

 

320x100

 

 

1. 글자 꺼꾸로 세기

 

"토스트의. 이런저런. 이야기들.xlsx"라는 이름에서 확장자의 위치를 알고 싶으면 뒤에서부터 세야 합니다.

함수를 먼저 보면 아래와 같습니다.

"=MATCH(".",MID(C3,LEN(C3)-ROW(INDIRECT("1:"&LEN(C3)))+1,1),0)"

 

이 함수는 배열함수임으로 입력할 때 ALT+SHIFT+ENTER로 입력해야 합니다.

앞의 다른 .을 무시하고 맨뒤의 .만 찾습니다.

 

확장자를 지우는 함수

 

"=MATCH(".",MID(C3,LEN(C3)-ROW(INDIRECT("1:"&LEN(C3)))+1,1),0)"로 확장자의 위치를 찾고,

"=LEFT(C3,LEN(C3)-C4)"로 확장자만 지우는 결과를 출력합니다.

 

 

 

2. 함수의 기능을 설명

 

배열함수기도하고 함수 자체가 워낙 복잡합니다.

"=MATCH(".",MID(C3,LEN(C3)-ROW(INDIRECT("1:"&LEN(C3)))+1,1),0)"

함수의 기능을 안쪽에서 바깥으로 나오면서 설명드리겠습니다.

하나씩 보면 이해할 수 있습니다.

 

함수 나누기

 

① ROW(INDIRECT("1:"&LEN(C3)))

 

INDIRECT 함수안에 범위를 넣으면 범위의 크기만한 배열을 만듭니다.

ROW 함수와 함께 사용하면 {1,2,3,4, ... Len(C3)}가 되어 글자의 길이만큼 범위를 확장합니다.

여기서 배열이 되었기 때문에 계속해서 배열로 계산됩니다.

 

② LEN(C3)-ROW(INDIRECT("1:"&LEN(C3)))+1

 

LEN(C3)가 글자의 길이임으로 이걸로 배열을 빼면 숫자가 역수가 됩니다.

LEN(C3)-ROW(INDIRECT("1:"&LEN(C3))) 함수의 결과는 {LEN(C3)-1, ... , 3,2,1,0}이 됩니다. 

LEN(C3)는 21이니, 21 - {1,2,3, ... ,21} = {20,19,18, ... , 0} 입니다.

여기에 +1을 하면 {21,20,19, ... , 1}가 됩니다.

 

③ MID(C3,LEN(C3)-ROW(INDIRECT("1:"&LEN(C3)))+1,1)

 

MID를 함수를 사용하면 한글자씩 뽑을 수 있습니다.

안에 배열함수가 역순으로 배열된 {21,20,19, ... , 1} 임으로 글자를 반대로 뽑습니다.

배열함수의 결과는 "xslx.들기야이 .런저런이 .의트스토"가 됩니다.

 

④ MATCH(".",MID(C3,LEN(C3)-ROW(INDIRECT("1:"&LEN(C3)))+1,1),0)

 

이렇게 꺼꾸로 배열된 글자를 MATCH함수로 찾으면 앞에서 찾습니다.

원본을 기준으로 뒤에서 부터 찾은 것과 같은 결과를 반영합니다.

이 경우에는 결과로 5를 반영합니다.

 

 

이제 복잡한 함수에 대해서 정리가 되었습니다.

확장자의 위치를 알았기 때문에 그것만 빼면 파일명이 됩니다.

"=LEFT(C3,LEN(C3)-5)" 전체 길이에서 확장자 위치를 빼고 왼쪽부터 출력하면 됩니다.

 

---

 

확장자를 예로 들어 설명을 했지만 뒤에서 부터 글자를 세는것은 여러가지로 편리할 수 있습니다.

외국인들의 이름나 무작위로 바코드 시스템같은 것들이 많습니다.