엑셀에서 하나의 셀 또는 셀 범위에서 특정 문자 개수를 세는 방법을 소개합니다. 문자는 'a', '가' 와 같이 하나의 글자를 의미하고 문자열은 "apple", "가나다" 와 같이 문자들이 결합된 것을 의미합니다. 이 글은 특정 문자열의 개수를 세는 방법에 대한 설명도 포함합니다.
설명을 그대로 따라서 해도 상관은 없지만 이왕이면 LEN 함수와 SUBSTITUTE 함수에 대한 이해가 전제되면 좋습니다.
LEN 함수는 단순히 문자열에서 문자의 개수를 세주는 함수입니다. 이 때 공백 문자 " " 도 하나의 문자로 취급되므로 주의해야 합니다.
SUBSTITUTE 함수는 문자열에서 특정 문자나 문자열을 다른 문자나 문자열로 변환시켜 새로운 문자열를 반환해주는 함수입니다.
엑셀 특정 문자 개수 세기
컨셉은 이렇습니다.
① LEN 함수를 이용해 소스가 되는 문자열에서 문자 전체의 개수를 세도록 합니다.
② ①의 결과에서 찾으려는 문자를 제외한 나머지 문자열의 개수를 빼면 됩니다. 찾으려는 문자를 제외한 나머지 문자를 세는 방법은 다음과 같습니다.
LEN(SUBSTITUTE(소스문자열, 찾으려는 문자, ""))
우선 LEN 함수의 내부에 붉은 수식을 해석해보면 -> 소스문자열에서 찾으려는 문자를 ""로 변환시킨 문자열을 반환합니다.
(주의: " "와 ""는 다릅니다. " "는 공백문자, ""는 아무런 문자도 아닙니다.)
그리고 LEN 함수로 반환된 문자열의 문자 개수를 셉니다.
③ ① - ② 를 하면 찾으려는 문자의 개수가 나옵니다.
위 설명을 대충 보시고 예제를 바로 보시길 바랍니다. 예제를 보면 쉽게 이해가 갑니다.
그럼 구체적인 예제를 보겠습니다.
셀 B2에서 문자 "a"의 개수를 찾는 예제입니다.
=LEN(B2) - LEN(SUBSTITUTE(B2, "a", ""))
LEN(B2)는 B2의 문자열 "apple ant absolute"에서 문자의 개수를 반환합니다. 문자 개수는 18이 되겠습니다.
LEN(SUBSTITUTE(B2, "a", "")) 는 B2의 문자열("apple ant absolute")에서 "a"를 ""로 대체한 후의 문자열 "pple nt bsolute" 의 문자 개수를 세어줍니다. 문자 개수는 15가 되겠습니다.
따라서 18 - 15는 3이됩니다.(문자 "a"의 개수)
엑셀 특정 문자열의 개수 세기
특정 문자열의 개수 역시 방법은 동일한데 마지막 결과에 특정 문자열(개수를 알려는 문자열)의 문자의 개수로 나누어 주어야 합니다. 왜그런지 예제를 통해 알아보겠습니다.
위 예는 셀 B3에서 "app"라는 문자열의 개수를 찾는 예입니다. 앞서 식과 동일하게 적용해보니 2가 나와야 되는데 6이라는 값이 나왔습니다.
=LEN(B3) - LEN(SUBSTITUTE(B3, "app", ""))
해석을 해보면 LEN(B3)에서 "application apple"의 문자의 개수 17을 반환합니다.
LEN(SUBSTITUTE(B3, "app", "")) 은 "lication le"에서 문자의 개수 11을 반환합니다.
따라서 17- 11은 6이됩니다.
이 결과는 "app"라는 문자열을 찾아서(두번 찾음) "app", "app" 의 문자의 개수를 센 것과 마찬가지 입니다. 따라서 찾으려는 문자열 "app"의 문자의 개수로 결과를 나누어야 합니다.
6/ LEN("app") 라고 하면 되겠습니다. 따라서 특정 문자열의 개수를 세려면 다음과 수식을 사용해야 합니다.
=(LEN(B3) - LEN(SUBSTITUTE(B3, "app", "")))/LEN("app")
엑셀 셀 범위 지정해서 특정 문자 또는 문자열 개수 새기
이 방법은 배열 수식에 대한 이해가 있어야 하지만 이해하기 힘들 수 있으므로 따라만 하셔도 됩니다. 문자와 문자열 찾는 방법의 차이는 앞에서 공부했으니 특정 문자열을 찾는 방법에 대한 예제만 보여드리겠습니다.
=SUM(LEN(B2:B4) - LEN(SUBSTITUTE(B2:B4, "apple", ""))) / LEN("apple")
붉은 수식은 앞서 예제들과 비슷하며 단지 소스 문자열을 택할 때 하나의 셀이 아닌 범위(B2:B4)를 지정했습니다.
이렇게 범위를 지정해서 배열 수식을 만들면 지정된 범위가 있는 수식은 각각의 셀들에 개별적으로 적용됩니다. 이렇게 개별적으로 적용된 결과값들을 sum 함수로 합칩니다. 그리고 LEN("apple")로 나누면 됩니다.
가장 중요한 건 배열 수식을 만들려면 수식을 완성한 후 수식입력창에서 CTRL+SHIFT+ENTER를 눌러줘야 합니다. 그러면 {=수식} 이런 식으로 중괄호가 적용됩니다. 배열수식이라는 뜻입니다.
이를 응용하면 엑셀 시트 전체에 있는 특정 문자열의 개수도 셀 수 있습니다.