이 글에서는 엑셀에서 특정 문자열이나 셀 범위 안에 특정 문자 또는 단어가 몇 번 나타나는지 세는 방법을 다룹니다. 대량의 데이터가 들어 있는 셀이나 데이터 범위에서 특정 문자의 빈도를 계산해야 하는 경우가 자주 있습니다. 이런 개수 계산 작업을 더욱 쉽게 처리할 수 있도록, 실무에서 바로 활용할 수 있는 간단한 수식들을 소개합니다.
연습용 워크북 다운로드
이 글을 준비하면서 사용한 연습용 워크북을 다운로드하여 함께 따라 해 보실 수 있습니다.
엑셀에서 문자열 내 문자 발생 횟수를 세는 5가지 방법
1. SUMPRODUCT와 LEN 함수로 문자열 내 전체 문자 개수 구하기
한 셀에 들어 있는 전체 문자 수를 알고 싶다면 LEN 함수만으로 충분합니다. 하지만 특정 범위에 포함된 전체 문자 수를 구하려면 SUMPRODUCT 함수와 LEN 함수를 함께 사용해야 합니다. 예를 들어 도서명 데이터 집합에서 해당 범위의 전체 문자 수를 확인하고 싶다고 가정해 보겠습니다. 아래 단계를 따라 해 보세요.
단계:
- 먼저 아래 수식을 입력합니다.
=SUMPRODUCT(LEN(B5:B9))LEN 함수는 텍스트 문자열의 문자 수를 반환하고, SUMPRODUCT 함수는 해당 범위 값들의 곱의 합을 반환합니다.
- 그러면 지정된 범위의 전체 문자 개수가 표시됩니다.
참고:
엑셀의 LEN 함수는 셀 안의 모든 문자를 세어 줍니다. 즉, 영문자와 숫자뿐 아니라 공백, 기호, 문장부호 등도 모두 포함하여 계산합니다.
2. SUBSTITUTE와 LEN 함수 조합으로 특정 문자 개수 세기 (대소문자 구분)
특정 셀 안에서 한 문자가 몇 번 나타나는지 확인해야 할 때가 있습니다. 이럴 때 LEN 함수와 SUBSTITUTE 함수를 동시에 활용할 수 있습니다. 주의할 점은 SUBSTITUTE 함수가 대소문자를 구분한다는 것입니다. 따라서 소문자를 찾으면 소문자만 개수에 포함됩니다. 예를 들어 도서명 데이터에서 소문자 'a'의 개수만 구해 보겠습니다.
단계:
- 먼저 아래 수식을 입력합니다.
=LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))
수식 분석:
➤ LEN(B5)
LEN 함수가 B5 셀의 문자 수를 계산합니다.
➤ SUBSTITUTE(B5,"a","")
SUBSTITUTE 함수가 B5 셀의 모든 'a' 문자를 빈 값("")으로 바꿉니다.
➤ LEN(SUBSTITUTE(B5,"a",""))
SUBSTITUTE 수식을 LEN 함수로 감싸면 'a'를 제외한 나머지 문자의 개수가 구해집니다.
➤ LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))
마지막으로 앞에서 구한 두 길이의 차이를 계산하면, 찾고자 하는 문자 'a'의 총 개수가 나옵니다.
- 최종 결과로 원하는 문자의 개수가 표시됩니다.
3. SUBSTITUTE와 LEN 함수 조합으로 특정 문자 개수 세기 (대소문자 구분 없이)
반대로 대소문자 구분 없이 문자 개수를 세어야 하는 경우도 있습니다. 이때는 앞선 수식에 UPPER 또는 LOWER 함수를 추가하면 됩니다. 이번에는 도서명 데이터에서 'A'와 'a'를 모두 찾아 보겠습니다.
단계:
- 먼저 아래 수식을 입력합니다.
=LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),"A",""))UPPER 함수는 텍스트 문자열을 모두 대문자로 변환합니다. 따라서 모든 'a'가 'A'로 바뀌어 대소문자를 구분하지 않고 개수를 셀 수 있습니다. 나머지 수식의 동작 방식은 앞의 예제와 동일합니다.
- 수식을 올바르게 입력했다면 아래와 같은 결과를 얻을 수 있습니다.
4. 범위에서 단일 문자의 발생 횟수 계산하기
때로는 하나의 셀이 아니라 문자열 범위 전체에서 특정 문자가 몇 번 나타나는지 구해야 할 수 있습니다. 예를 들어 데이터 범위에서 'A' 또는 'a'의 빈도를 알고 싶은 경우입니다.
단계:
- 먼저 아래 수식을 입력합니다.
=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"a","")))SUM 함수는 셀 범위 안의 모든 숫자를 더합니다. 나머지 부분은 앞서 설명한 방식과 같게 동작하며, 최종적으로 전체 범위에서 문자 'a'의 총 개수를 반환합니다.
- 결과적으로 아래와 같이 총 개수를 확인할 수 있습니다.
5. 범위에서 특정 텍스트(부분 문자열)의 발생 횟수 세기
앞의 방법과 마찬가지로, 데이터 범위에서 특정 텍스트가 몇 번 등장하는지 알고 싶은 경우가 있습니다. 이럴 때는 여러 함수를 조합해서 사용할 수 있습니다. 이번 방법에서는 SUM, LEN, SUBSTITUTE 함수를 함께 사용합니다. 예를 들어 색상 이름이 들어 있는 데이터 범위에서 'Green'이라는 색상이 몇 번 나타나는지 구해 보겠습니다.
단계:
- 먼저 아래 수식을 입력합니다.
=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"Green","")))/LEN("Green")위 수식에서 반드시 문자 개수를 지정한 텍스트(부분 문자열)의 길이로 나누어야 합니다. 그렇지 않으면 텍스트를 구성하는 각 문자가 개별적으로 계산되어 잘못된 결과가 나옵니다.
- 마지막으로 지정된 범위에서 'Green'의 총 개수가 표시됩니다.
참고:
위 수식은 배열 수식으로 입력해야 합니다. 윈도우용 엑셀에서 배열 수식으로 입력하려면 CTRL+SHIFT+ENTER 키를 누르세요.
마무리
지금까지 엑셀에서 문자열 또는 문자열 범위 내 문자 개수를 구하는 쉽고 실용적인 방법들을 살펴보았습니다. LEN, SUMPRODUCT, SUBSTITUTE 함수의 조합만 익혀두면 대소문자 구분 여부와 관계없이 원하는 결과를 정확하게 얻을 수 있습니다. 이 글이 도움이 되었기를 바라며, 방법에 대해 궁금한 점이 있다면 언제든지 문의해 주세요.
관련 글
- 엑셀에서 문자열 내 특정 문자 찾는 방법
- 엑셀에서 범위 내 값의 첫 번째 발생 위치 찾기 (3가지 방법)
- 엑셀 FIND 함수 활용법 (7가지 예제)
- 엑셀 LEN 함수 사용법 (수식 및 VBA 코드)