마지막 반복 문자 이후의 문자열 추출하기 (1)

1. 문제

 

아래와 같은 폴더명에서 파일명만을 분리해내려고 합니다.

구분자는 ￦입니다.

 

특정문자의 마지막위치이후 문자열 가져오기.xlsx

0.01MB

 

 

2. 해결방안

 

(1) 해결 방안 1 : Find 함수 반복 사용

Find  함수의 구문은 FIND(find_text, within_text, [start_num])로서

찾을 문자열, 범위, [시작 위치]인데,

시작 위치를 다시 Find함수를 이용해서 지정할 수 있습니다.

 

A2셀의 경우 ￦가 다섯 번 반복되므로 ￦ 찾는 것을 다섯 번만 해야 29란 숫자가 나오지,

 

여섯 번 하면 #VALUE! 에러가 발생합니다.

 

따라서, ￦의 개수만큼 find 함수를 사용해야 하는데 그렇게 수식을 작성할 수 있는 방법이 없습니다.

 

IfError 함수를 사용하려고 해도 이 긴 수식을 개수에 맞게 넣어야 하는데 그건 가독성이 떨어지고 일이 더 많아집니다.

 

 

(2) 해결 방안 2 : 숫자 배열 이용

시작위치를 숫자 배열로 지정할 수 있습니다.

365 버전이라 아래와 같이 배열로 반환됩니다.

 

값은 3,7,15,20,29 다섯 개이고, 그 이후는 ￦를 찾을 수 없어서 #VALUE! 에러가 발생합니다.

 

그렇다면 가장 큰 위치를 구하려면 Max함수를 사용하면 됩니다.

=MAX(FIND("\",A2,{1,5,10,15,20,25,30,35,40,45,50}))를 사용했는데, 에러 값 때문에 #VALUE! 에러가 발생합니다.

 

따라서, Max 다음에 IfError함수를 사용해서 에러일 때 값을 0으로 지정합니다. 그러면 최댓값을 구하는 것이므로 영향을 주지 않습니다.

 

숫자 배열을 지정하는 것이 좀 번거롭기는 하지만 ￦의 마지막 위치를 구했으므로

파일명을 =mid(a2,29+1,50)로 지정해서 구할 수 있습니다.

여기서 가져올 문자의 길이를 50이라고 준 것은 넉넉하게 가져오기 위한 것입니다.

 

29를 수식으로 대치하면

=MID(A2,MAX(IFERROR(FIND("\",A2,{1,5,10,15,20,25,30,35,40,45,50}),0))+1,50)

이 됩니다.

 

오른쪽의 공백은 Trim 함수를 사용하지 않아도 자동으로 제거되었습니다.

 

(3) 해결 방안 3 : Sequence 함수 사용

Sequence함수의 구문은

=SEQUENCE(rows,[columns],[start],[step])로서

행 수, 열 수, 시작 값, 간격으로 구성되어 있습니다. 

=SEQUENCE(10,,1,5)라고 행을 10행, 열은 기본값이 1이므로 지정하지 않고,

시작값을 1, 간격을 5라고 지정하면 아래와 같이 1, 6, 11식으로 해서 46까지 배열로 반환됩니다.

 

이제 숫자 배열을 Sequence함수로 대체하면

=MID(A2,MAX(IFERROR(FIND("\",A2,SEQUENCE(10,,1,5)),0))+1,50)

같은 값이지만 훨씬 수식이 깔끔해졌습니다.

 

(4) 해결 방안 4 : Substitute와 Rept 함수 사용

Substitute 함수의 구문은

SUBSTITUTE(text, old_text, new_text, [instance_num])으로서

문자, 바꿀 문자, 새 문자, 반복 횟수로 되어 있습니다.

 

여기서 핵심은 ￦를 공백 한 칸으로 바꾸는 것이 아니라 50개 정도로 길게 잡아야 한다는 것입니다.

다시 말해 ￦를 공백 50칸으로 바꾼 다음 오른쪽에서 문자 50개를 가져오면 왼쪽에 공백은 있지만 ￦는 안 들어가게 됩니다.

 

수식은 =RIGHT(SUBSTITUTE(A2,"\",REPT(" ",50)),50)이고,

결괏값은 아래와 같이 ￦이후의 문자를 가져오기는 했는데, 왼쪽에 공백이 많습니다.

 

공백은 Trim함수를 이용해 제거하면 됩니다.

따라서 완성된 수식은 =TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",50)),50))입니다.

 

네이버 카페 소림권법님의 수식을 가져왔습니다.

특정문자의 마지막위치이후 문자열 가져오기(완성).xlsx

0.01MB