Excel에서 셀에 특정 텍스트가 포함되어 있는지 확인하는 방법 [6가지]

Excel 데이터로 작업하다 보면 키워드를 찾거나 레코드를 필터링하거나 정보를 분류하기 위해 셀에 특정 텍스트가 포함되어 있는지 확인해야 할 때가 있습니다. 예를 들어 재고 없음으로 표시된 제품을 식별하거나 특정 용어가 포함된 셀을 강조 표시할 수 있습니다.

이 가이드에서는 SEARCH, FIND, COUNTIF, 조건부 서식, 그리고 Free Spire.XLS for Python을 사용한 Python 자동화를 활용하여 Excel에서 셀에 특정 텍스트가 포함되어 있는지 확인하는 방법을 설명합니다.

SEARCH와 ISNUMBER로 셀에 특정 텍스트가 포함되어 있는지 확인하기

Excel에서는 SEARCH와 ISNUMBER의 조합이 셀에 특정 텍스트가 포함되어 있는지 확인하는 일반적인 방법입니다. 이 수식은 한 번에 하나의 셀만 평가하므로 보조 열에 적용하고 수식을 아래로 채워야 합니다.

예를 들어 제품 설명이 H열에 저장되어 있다면 I2 셀에 수식을 입력합니다:

=ISNUMBER(SEARCH("Out of stock",H2))

그런 다음 수식을 아래로 복사하여 범위의 나머지 행에도 동일한 검사를 적용합니다.

이 수식은 해당 셀에 out of stock이 포함된 행에는 TRUE를, 키워드가 없는 행에는 FALSE를 반환합니다.

SEARCH를 ISNUMBER와 함께 사용하는 이유는?

SEARCH 함수는 셀 내에서 특정 텍스트의 위치를 찾습니다. 일치하는 항목을 찾으면 텍스트의 시작 위치를 반환합니다. 예를 들어 =SEARCH("stock","Out of stock")은 stock이 9번째 문자에서 시작하므로 9를 반환합니다.

대상 텍스트가 없으면 SEARCH는 #VALUE! 오류를 반환합니다. 이를 ISNUMBER와 결합하면 이 결과를 단순한 TRUE/FALSE 값으로 변환할 수 있습니다:

• SEARCH가 숫자를 반환하면 ISNUMBER는 TRUE를 반환합니다.
• SEARCH가 오류를 반환하면 ISNUMBER는 FALSE를 반환합니다.

이 방법은 필터를 만들거나, 조건부 서식을 추가하거나, 추가 처리를 위해 레코드를 표시하는 등 각 행에 대해 명확한 일치 결과가 필요할 때 유용합니다.

FIND와 ISNUMBER로 대소문자를 구분하여 검색하기

어떤 상황에서는 대문자와 소문자가 중요합니다. 이는 서로 다른 대소문자가 서로 다른 값을 나타내는 제품 코드, 내부 식별자 또는 카테고리를 다룰 때 흔히 발생합니다.

FIND 함수는 SEARCH와 유사하게 작동하지만 대소문자를 구분하여 검색합니다. FIND를 ISNUMBER와 결합하면 대소문자 차이를 유지하면서 셀에 특정 텍스트가 포함되어 있는지 확인할 수 있습니다.

예를 들어 제품 상태 열에 "out of stock"과 "Out of stock" 같은 값이 포함되어 있다고 가정해 보겠습니다. 정확히 Out of stock 대소문자를 사용하는 셀만 식별하려면 보조 열에 다음 수식을 사용하여 전체 데이터 범위에 적용합니다:

=ISNUMBER(FIND("Out of stock",H2))

이 수식은 해당 셀에 "Out of stock"이 포함되어 있으면 TRUE를, "out of stock"이나 다른 텍스트가 포함되어 있으면 FALSE를 반환합니다. SEARCH와 달리 FIND는 대문자와 소문자를 구분하므로 대소문자를 구분하는 텍스트 검사에 적합합니다.

SEARCH vs FIND

Excel에서 COUNTIF와 와일드카드로 셀에 특정 텍스트가 포함되어 있는지 확인하기

더 짧은 수식을 선호한다면 COUNTIF가 셀에 특정 텍스트가 포함되어 있는지 확인하는 간단한 방법을 제공합니다. 이 방법은 이미 Excel 조건 수식에 익숙하고 여러 함수를 결합하는 것을 피하고 싶을 때 유용합니다.

SEARCH와 달리 COUNTIF는 *와 같은 와일드카드 문자를 사용하여 텍스트 패턴을 일치시킬 수 있습니다. 따라서 기본적인 키워드 검사에 편리합니다.

보조 열의 셀에 다음 수식을 입력합니다:

=COUNTIF(H2,"Out of stock")>0

이 수식은 셀에 "out of stock"이 포함되어 있으면 TRUE를, 지정한 텍스트가 없으면 FALSE를 반환합니다.

별표(*)의 작동 방식

COUNTIF에서 별표(*)는 임의 개수의 문자를 나타내는 와일드카드입니다. 대상 텍스트 앞뒤에 별표를 배치하면 Excel이 셀 안 어디에 있든 키워드를 일치시킬 수 있습니다.

예를 들어 stock을 검색 패턴으로 사용하면 stock만 포함된 셀은 물론 "out of stock"이나 "low stock warning"과 같은 더 긴 문구가 포함된 셀도 일치합니다. 와일드카드 문자를 사용하면 키워드 앞뒤에 어떤 텍스트든 올 수 있기 때문입니다.

팁: 단순히 일치 여부를 확인하는 대신 텍스트가 포함된 셀의 개수를 세야 한다면, Excel에서 텍스트가 포함된 셀 개수 세기 가이드를 참고하세요.

Excel에서 OR 논리로 여러 키워드 확인하기

경우에 따라 셀에 여러 키워드 중 하나가 포함되어 있는지 확인해야 할 수 있습니다. 예를 들어 제품 상태를 분석할 때 재고가 없거나 판매 실적이 저조한 항목을 식별하고 싶을 수 있습니다. OR 함수를 사용하면 하나의 수식에서 여러 텍스트 검사를 결합할 수 있습니다. 앞의 방법들과 유사합니다.

보조 열의 셀에 다음 수식을 입력합니다:

=OR(ISNUMBER(SEARCH("out of stock",H2)),ISNUMBER(SEARCH("Poor-selling",H2)))

이 수식은 해당 상태에 "out of stock" 또는 "Poor-selling"이 포함되어 있으면 TRUE를 반환합니다. 정상 상태나 다른 값에 대해서는 FALSE를 반환합니다.

조건부 서식으로 특정 텍스트가 포함된 셀 강조하기

보조 열을 사용하는 것은 셀에 특정 텍스트가 포함되어 있는지 확인하는 실용적인 방법이지만, 결과를 표시할 추가 열이 필요합니다. 데이터 구조를 변경하지 않고 일치하는 레코드를 더 잘 보이게 하고 싶다면 조건부 서식이 더 나은 선택입니다.

조건부 서식을 사용하면 Excel에서 특정 규칙을 충족하는 셀을 자동으로 강조 표시할 수 있습니다. SEARCH를 기반으로 한 수식을 사용하면 데이터가 변경될 때마다 일치하는 셀을 강조 표시할 수 있습니다.

조건부 서식을 적용하려면 다음 단계를 따르세요:

• 1단계: H2:H15와 같은 대상 범위를 선택합니다.
• 2단계: 홈 > 조건부 서식 > 새 규칙으로 이동합니다.

• 3단계: 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.
• 4단계: =ISNUMBER(SEARCH("Out of stock",H2)) 수식을 입력합니다.

• 5단계: 서식...을 클릭하고 서식 스타일을 선택한 다음 확인을 클릭하여 규칙을 적용합니다.

Python으로 셀에 특정 텍스트가 포함되어 있는지 자동으로 확인하기

수식 기반 방법은 개별 스프레드시트를 처리할 때는 잘 작동하지만, 반복되는 보고서나 많은 수의 Excel 파일을 처리할 때 수식이나 서식 규칙을 수동으로 추가하는 것은 비효율적일 수 있습니다.

자동화된 워크플로에서는 개발자가 Python을 사용하여 동일한 텍스트 확인 로직을 프로그래밍 방식으로 적용할 수 있습니다. Free Spire.XLS for Python을 사용하면 Microsoft Excel에 의존하지 않고 Excel 통합 문서를 만들고, 편집하고, 서식을 지정할 수 있습니다.

Free Spire.XLS for Python이란?

Free Spire.XLS for Python은 Excel 통합 문서 작업을 위한 Python 라이브러리입니다. 파일 생성, 워크시트 읽기 및 편집, 수식 적용, 조건부 서식 추가, Excel 문서 변환 등 일반적인 스프레드시트 작업을 지원합니다.

이 라이브러리를 사용하면 개발자가 여러 보고서에 텍스트 기반 서식 규칙을 적용하는 등 반복적인 스프레드시트 작업을 자동화할 수 있습니다.

단계별 Python 구현

다음 예제에서는 Excel 통합 문서를 로드하고 셀에 특정 문구가 포함되어 있는지 여부에 따라 조건부 서식 규칙을 자동으로 적용하는 방법을 보여줍니다.

1단계: 라이브러리 설치

pip를 사용하여 패키지를 설치합니다:

pip install Spire.Xls.Free

2단계: Python으로 조건부 서식 적용

다음 스크립트는 Excel 파일을 로드하고 out of stock이 포함된 셀을 강조 표시합니다.

from spire.xls import Workbook, Color

file_path = "input/sales report.xlsx"
output_path = "output/Highlighted.xlsx"

# Load the Excel workbook
workbook = Workbook()
workbook.LoadFromFile(file_path)
sheet = workbook.Worksheets[0]

# Target the desired cell range
range_data = sheet.Range["H2:H15"]

# Add an expression-based conditional formatting rule
rule = range_data.ConditionalFormats.AddCondition()
rule.FormatType = ConditionalFormatType.Formula
rule.FirstFormula = '=ISNUMBER(SEARCH("Out of stock", H2))'

# Apply background color for matching cells
rule.BackColor = Color.get_LightPink()

# Save output and release resources
workbook.SaveToFile(output_path)
workbook.Dispose()

자주 발생하는 실수와 FAQ

Q1: 수식이 #VALUE! 오류를 반환하는 이유는?

SEARCH나 FIND를 단독으로 사용했는데 셀에 지정한 텍스트가 없으면 Excel은 #VALUE! 오류를 반환합니다. 텍스트 존재 여부를 판단해야 한다면 ISNUMBER를 사용하여 검색 결과를 TRUE 또는 FALSE로 변환하세요.

Q2: 특정 텍스트 확인과 ISTEXT()의 차이점은?

ISTEXT 함수는 셀에 텍스트 데이터가 포함되어 있는지 확인하므로 텍스트 값과 숫자, 날짜 및 기타 데이터 유형을 구분하는 데 도움이 됩니다. 예를 들어 =ISTEXT(A1)은 셀에 텍스트가 포함되어 있으면 TRUE를, 숫자나 다른 데이터 유형이 포함되어 있으면 FALSE를 반환합니다.

그러나 ISTEXT는 셀에 특정 텍스트가 포함되어 있는지 확인할 수 없습니다. 특정 단어나 문구를 검색해야 한다면 대신 SEARCH, FIND 또는 COUNTIF를 사용하세요.

Q3: 셀에 부분 텍스트가 포함되어 있는지 어떻게 확인할 수 있나요?

셀에 더 긴 텍스트 문자열의 일부가 포함되어 있는지 확인하려면 ISNUMBER와 함께 SEARCH를 사용하거나 와일드카드와 함께 COUNTIF를 사용하세요. 예를 들어 =ISNUMBER(SEARCH("stock",A1))은 셀 텍스트 어디에든 stock이 포함되어 있으면(예: "out of stock" 또는 "low stock"과 같은 문구) TRUE를 반환합니다. 더 짧은 와일드카드 기반 수식으로 =COUNTIF(A1,"stock")>0을 사용할 수도 있습니다.

마무리

셀에 특정 텍스트가 포함되어 있는지 확인하는 것은 Excel 데이터 처리에서 흔한 작업입니다. 대부분의 경우 ISNUMBER(SEARCH())가 유연한 해결책을 제공하며, FIND()는 대문자와 소문자를 구분해야 할 때 유용합니다. 더 짧은 수식을 선호한다면 COUNTIF()가 간단한 와일드카드 기반 접근 방식을 제공합니다.

반복되는 스프레드시트 워크플로의 경우 Free Spire.XLS for Python을 사용한 Python 자동화를 통해 동일한 텍스트 확인 및 서식 규칙을 프로그래밍 방식으로 적용하여 반복적인 수작업을 줄일 수 있습니다.

함께 읽어보세요:

• Excel에서 두 열을 비교하는 방법 – 7가지 쉬운 방법
• Excel에서 중복 항목 강조 표시: 4가지 쉬운 방법
• Excel에서 필터 지우기 또는 제거하기: 6가지 방법 (수동 및 자동)
• Excel에서 데이터 정렬하기: 5가지 간단하고 고급스러운 방법 설명