엑셀 텍스트에서 단어만 찾기 FIND, SEARCH 함수 함정과 해결책

 

 

엑셀 텍스트에서 단어만 찾기: FIND, SEARCH 함수 함정과 해결책

엑셀에서 특정 단어만 찾으려는데 자꾸 문자까지 걸려서 짜증 나시죠?! FIND, SEARCH 함수만 써서는 안 됩니다! MATCH, XMATCH, TEXTSPLIT, FILTER 함수와 정규 표현식까지 활용하는 궁극의 엑셀 단어 검색 비법 대방출~!! 지금 바로 엑셀 마스터로 레벨업하세요! ^^

1. FIND, SEARCH 함수: 텍스트 검색의 기본, 하지만 함정이?!

FIND, SEARCH 함수! 텍스트 안에서 특정 문자열 찾을 땐 정말 유용하죠. SEARCH는 대소문자 구분 없이 쓴다는 점만 빼면, 둘 다 거의 똑같아요. 하지만, "차"라는 단어를 찾으려는데 "차량", "차가운"까지 몽땅 나온다면?! 으악! 이건 FIND, SEARCH 함수가 문자열만 찾지, 단어는 구분 못 해서 생기는 문제입니다.

1.1. FIND 함수

FIND 함수는 대소문자를 구분하여 문자열의 위치를 반환합니다. 찾고자 하는 문자열이 없으면 #VALUE! 오류를 반환하는 점에 유의해야 합니다. 예를 들어, `=FIND("apple","apple pie")`는 1을 반환합니다.

1.2. SEARCH 함수

SEARCH 함수는 대소문자를 구분하지 않고 문자열의 위치를 반환합니다. 마찬가지로, 찾는 문자열이 없으면 #VALUE! 오류를 반환합니다. 예를 들어, `=SEARCH("Apple","apple pie")`는 1을 반환합니다.

2. 단어 단위 검색: MATCH, XMATCH, TEXTSPLIT 함수의 환상적인 콜라보!

FIND, SEARCH의 함정을 탈출하려면? MATCH, XMATCH, TEXTSPLIT 함수가 정답입니다! TEXTSPLIT 함수는 텍스트를 지정한 구분 기호로 쪼개서 배열로 만들어 줘요. 콤마(,), 공백, 슬래시(/) 등등, 원하는 대로 쓸 수 있죠. 이렇게 쪼갠 배열을 MATCH나 XMATCH 함수에 넣으면, 단어 단위로 쏙쏙 검색할 수 있습니다. 특히 XMATCH 함수는 역방향 검색, 부분 일치 검색 등 훨씬 다양한 기능을 제공하니, 꼭 활용해 보세요!

2.1. TEXTSPLIT 함수 활용

TEXTSPLIT 함수는 텍스트 문자열을 특정 구분 기호를 기준으로 분할하여 배열로 반환합니다. 콤마(,), 공백, 탭 등 다양한 구분 기호를 사용할 수 있으며, 여러 개의 구분 기호를 동시에 지정할 수도 있습니다. 예를 들어, `=TEXTSPLIT("apple,banana,orange",",")`는 "apple", "banana", "orange"를 각각 포함하는 배열을 반환합니다.

2.2. MATCH, XMATCH 함수와의 조합

TEXTSPLIT 함수로 분할된 배열을 MATCH 또는 XMATCH 함수의 lookup_array 인수로 사용하면, 각각의 단어를 독립적인 요소로 검색할 수 있습니다. `=XMATCH("banana",TEXTSPLIT("apple,banana,orange",","),0)`는 "banana"가 두 번째 요소이므로 2를 반환합니다. XMATCH 함수는 MATCH 함수보다 더욱 다양한 매칭 모드를 제공합니다. 예를 들어, `match_mode` 인수를 1로 설정하면 다음으로 큰 항목을, -1로 설정하면 다음으로 작은 항목을 찾습니다.

3. FILTER 함수와 정규 표현식: 엑셀 검색의 끝판왕!

FILTER 함수와 정규 표현식을 함께 쓰면 진짜 엄청난 검색 기능을 만들 수 있어요! 정규 표현식은 특정 패턴을 가진 문자열을 찾는 데 쓰는 강력한 도구인데요, 예를 들어 "\b차\b"는 "차"라는 단어의 앞뒤에 단어 경계(\b)가 있는 경우에만 일치하는 패턴입니다. "차량", "차가운" 같은 건 안 걸리고 "차"만 딱 찾아주는 거죠! 이 정규 표현식을 FILTER 함수의 조건으로 쓰면, 원하는 패턴을 가진 단어만 쏙쏙 골라낼 수 있습니다.

3.1. 정규 표현식의 기초

정규 표현식은 특정 패턴을 가진 문자열을 검색하는 데 사용되는 강력한 도구입니다. 다양한 메타 문자를 사용하여 복잡한 패턴을 정의할 수 있습니다. 예를 들어, `.`은 임의의 한 문자를, `*`은 앞 문자의 0회 이상 반복을, `+`는 앞 문자의 1회 이상 반복을 나타냅니다.

3.2. FILTER 함수와의 조합: 실전 예제

`=FILTER(TEXTSPLIT(A1," "),ISNUMBER(SEARCH("\b차\b",TEXTSPLIT(A1," "))))` 이 수식은 A1 셀의 텍스트를 공백으로 분할하고, "\b차\b" 정규 표현식과 일치하는 단어만 추출합니다. 만약 A1 셀에 "나는 차를 타고 차가운 바람을 맞으며 차로 갔다"라는 문장이 있다면, 결과는 "차", "차로"를 포함하는 배열이 됩니다. "차가운"은 단어 경계가 일치하지 않으므로 제외됩니다.

4. 실무 활용 팁: 깨끗하고 정확한 데이터를 위한 추가 전략

• 대소문자 구분: FIND 함수나 XMATCH 함수의 match_mode를 조절해서 대소문자 구분 여부를 설정할 수 있어요.
• 여러 단어 검색: OR 함수나 SUMPRODUCT 함수를 활용하면 여러 단어를 한 번에 검색할 수 있습니다.
• 부분 일치 검색: 정규 표현식의 와일드카드 문자(*, ?)를 사용하면 부분 일치 검색도 가능합니다.
• TRIM 함수: 텍스트 앞뒤의 불필요한 공백은 TRIM 함수로 제거하고 검색하면 더 정확한 결과를 얻을 수 있습니다. TRIM 함수는 셀의 내용에서 선행 및 후행 공백을 제거하고, 중간에 있는 여러 개의 공백을 하나의 공백으로 변환합니다.
• CLEAN 함수: CLEAN 함수는 인쇄할 수 없는 문자를 제거하여 데이터 정확도를 향상시키는 데 유용합니다. CLEAN 함수는 텍스트 문자열에서 ASCII 코드 0에서 31까지의 인쇄할 수 없는 문자를 제거합니다. 이러한 문자는 종종 다른 응용 프로그램에서 복사된 텍스트에 나타나며, 엑셀에서 문제를 일으킬 수 있습니다.
• SUBSTITUTE 함수: 특정 문자를 다른 문자로 바꿔야 할 때 SUBSTITUTE 함수를 사용하면 됩니다. 예를 들어, 하이픈(-)을 공백으로 바꾸려면 `=SUBSTITUTE(A1,"-"," ")`와 같이 사용합니다.
• LOWER, UPPER, PROPER 함수: 텍스트의 대소문자를 변경해야 할 때는 LOWER, UPPER, PROPER 함수를 사용하세요. LOWER는 모든 문자를 소문자로, UPPER는 모든 문자를 대문자로, PROPER는 각 단어의 첫 글자를 대문자로 변환합니다.

5. 마무리: 엑셀 텍스트 분석, 이제 두렵지 않아요!

FIND, SEARCH 함수는 텍스트 검색의 기본이지만, 단어 검색은 조금 까다로웠죠? 하지만 이제 TEXTSPLIT, MATCH, XMATCH, FILTER 함수, 그리고 정규 표현식까지 마스터했으니, 엑셀 텍스트 분석도 문제없습니다! 이 팁들을 잘 활용해서 엑셀 실력을 한 단계 업그레이드해 보세요! 다음에는 더 놀라운 엑셀 팁으로 돌아오겠습니다! 기대해 주세요~! ^^