엑셀의 텍스트 문자열에서 특정 위치의 단어를 추출해야 할 경우가 있습니다.
예를 들어 텍스트 문자열로 이름, 나이, 취미, 특기, 연락처 등을 정리해서 만들어 놓은 시트에서 3번째 위치에 있는 취미만 추출하거나 5번째에 있는 연락처만 추출해야 하는 경우 어떻게 하면 쉽게 추출할 수 있을까요?
각 단어의 문자 길이가 동일하면 문자열을 원하는 길이로 잘라서 추출할 수 있지만 각 단어의 길이가 서로 다를 경우 어떻게 추출해야 할까 고민하게 됩니다.
이번 글에서는 엑셀의 셀 텍스트 문자열에서 N번째 단어를 추출 하는 방법에 대해 알아보겠습니다.
엑셀 수식을 사용하여 텍스트 문자열에서 n 번째 단어를 추출하는 방법
엑셀에서 제공하는 몇 가지 함수를 조합해서 사용하면 텍스트 문자열에서 n 번째 단어를 쉽게 추출할 수 있습니다.
1. 사용되는 함수는 아래와 같습니다.
- TRIM : 텍스트의 양 끝 공백을 없앱니다.
- MID : 문자열의 지정 위치에서 문자를 지정한 개수만큼 돌려줍니다.
- SUBSTITUTE : 텍스트 중의 old_text를 찾아서 new_text로 바꿉니다.
- REPT : 텍스트를 지정한 횟수만큼 반복합니다.
- LEN : 텍스트 문자열 내의 문자 개수를 구합니다.
2. 위의 함수를 이용해서 텍스트 문자열에서 N번째 단어를 추출하는 수식을 만들어 보겠습니다.
- =TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))
- A2는 텍스트 문자열 셀입니다.
- B2는 추출할 단어의 위치를 지정한 셀입니다.
위치를 직접 입력해도 됩니다. 직접 입력할 때 원하는 위치에서 빼기 -1한 숫자를 입력해 줍니다.
3번째 단어를 추출해야 하는 경우 “2”를 입력합니다.
3. 처리 순서대로 함수를 풀어서 설명하면 다음과 같습니다.
- REPT(” “, LEN(A2)) : 공백을 A2 텍스트 문자열의 문자 개수만큼 만듭니다.
결과적으로 13개의 공백(” “)이 만들어지게 됩니다. - SUBSTITUTE(A2, ” “, REPT(” “, LEN(A2)))
=> SUBSTITUTE(A2, ” “, ” “)
결과적으로 “무궁화 꽃이 피었습니다.”로 반환하게 됩니다. - (B2-1)*LEN(A2)+1 : 14를 반환합니다.
- LEN(A2) : 13을 반환합니다.
- MID(SUBSTITUTE(A2,” “,REPT(” “,LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2))
=> MID(“무궁화 꽃이 피었습니다.”, 14, 13)
결과적으로 ” 꽃이 “이 반환됩니다. - TRIM(MID(SUBSTITUTE(A2,” “,REPT(” “,LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))
=> TRIM(” 꽃이 “)
결과적으로 “꽃이” 반환하게 됩니다.
문자열에서 단어 사이의 공백이 중복될 때 올바르게 N번째 단어를 추출하는 방법
만약 텍스트 문자열에서 단어 사이의 공백이 중복될 경우 위의 수식으로만 사용하면 원하는 n번째 단어를 추출할 수 없게 됩니다.
▲ 앞 단락의 수식을 사용했을 때 중복된 공백으로 인해 추출된 결과가 잘못 된 것을 확인할 수 있습니다.
단어 사이에 공백(Space)가 중복될 때는 아래와 같이 수식을 조금 변경해 주면 됩니다.
- =TRIM(MID(SUBSTITUTE(TRIM(A5),” “,REPT(” “,LEN(A5))), (B5-1)*LEN(A5)+1, LEN(A5)))
마무리
이전 포스팅 글에서 엑셀에서 셀의 텍스트 문자열에서 첫 번째 단어를 추출하는 3가지 간단한 방법과 엑셀에서 셀의 텍스트 문자열에서 마지막 단어를 추출하는 3가지 간단한 방법에 대해 설명한 적이 있습니다.
셀의 텍스트 문자열에서 단어를 추출하거나 위치를 이동하거나 특정 단어를 제거하는 등의 작업을 하는 경우가 종종 있게 됩니다.
이전에 올린 글과 이번 글을 참고하면 원하는 위치의 단어를 쉽게 추출해서 사용할 수 있습니다.
※ 함께 읽으면 도움이 될 만한 다른 포스팅 글입니다.
이 글이 도움이 되었기를 바랍니다. ^-^