특정 문자 기준 몇 번째까지 문자열 가져오기

1. 문제

아래와 같이 빨간 글자 전까지만 글자를 가져오는 것을 수식으로 만들어보겠습니다.

색 기준 분리.xlsx

0.01MB

 

2. 해법

기준은 뒤에서 두 번째 콜론까지 가져오는 것입니다.

 

가. Substitute 함수 사용

(1) 몇 번째부터 몇 번째 + 1번째 사이의 문자열 추출하기

몇 번째부터 몇 번째 + 1번째까지의 문자열을 추출하는 것은 50개의 공백을 삽입한 다음 50*번째부터 50개의 문자를 추출한 다음 좌, 우 공백을 제거합니다.

위 데이터를 기준으로 세 번째 콜론과 네 번째 콜론 사이의 문자를 추출하는 것은

=TRIM(MID(SUBSTITUTE(D4,":",REPT(" ",50)),50*3,50))

로 구합니다.

 

(2) 처음부터 몇 번째까지의 문자열 추출하기

이것은 특정 문자를 네 번째 위치에 삽입한 다음 그 문자를 찾아서 -1까지를 가져오면 됩니다.

특정 문자를 삽입하는 것은 Substitute 함수를 이용해서 네 번째 인수의 위치에 해당하는 숫자를 입력하면 됩니다.

다시 말해 아래와 같이 네 번째 인수 instance_num에 4를 입력하면 됩니다.

 

그러면 아래와 같이 네번째 콜론이 @문자로 바뀝니다.

 

따라서, @문자 전까지 문자열을 가져오면 되므로

=LEFT(D4,FIND("@",SUBSTITUTE(D4,":","@",4)))

라고 입력하면 됩니다.

 

그런데 comment1을 잘 살펴보면 빨간색 글자 전까지의 콜론 위치가 네 번째 또는 다섯 번째로 다르지만, 뒤에서 두 번째인 것은 동일합니다.

따라서, 콜론의 개수를 구한 다음 -1의 위치에 @를 넣으면 됩니다.

콜론의 개수는 문자열의 길이에서 콜론을 공백으로 대체한 문자열의 길이를 빼면 됩니다.

수식은 =LEN(D5)-LEN(SUBSTITUTE(D5,":","")) 입니다.

이제 @문자를 넣는 위치를 4에서 '위 수식-1'로 바꾸면 됩니다. D5셀의 수식을 복사하면 D5가 되므로 D4로 수정해야 하는 점 주의해야 합니다.

완성된 수식은 =LEFT(D4,FIND("@",SUBSTITUTE(D4,":","@",LEN(D4)-LEN(SUBSTITUTE(D4,":",""))-1))) 입니다.

 

나. TextBefore 함수 사용

(1) 구문

=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

instance_num이 음수인 경우가 거의 없는데 이 함수의 경우는 음수가 지원되며, 음수는 뒤에서부터 거꾸로 찾아오는 것입니다.

 

다시 말해 -1이라고 하면 뒤에서 첫 번째 전까지의 문자열을 반환합니다.

그런데, =TEXTBEFORE(D4,":",-1)라고 입력하면 콜론을 기준으로 맨 뒤에서 첫 번째 콜론 전까지의 문자열만 가져오므로

 

-2라고 입력한 다음 :을 &연산자로 연결해야 합니다.

완성된 수식은 =TEXTBEFORE(D4,":",-2)&":" 입니다.

 

색 기준 분리(완성).xlsx

0.01MB