엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

엑셀(Excel)/Excel

엑셀 Excel 특정 문자 세는 방법, LENSUBSTITUTE 함수 사용

 

환경: Microsoft Excel 2013

 

문자열에서 어떻게 하면 특정 문자의 개수를 알아 낼 수 있을까요? 엑셀에는 특정 문자 개수를 세어 주는 함수가 없습니다. 다른 함수들을 이용해서 값을 찾아야 하는데, 문자의 길이 값을 알아내는 LEN 과 문자를 교체하는 SUBSTITUTE 함수 조합으로 가능합니다.

 

1. 특정 문자 개수 세는 방법

 

답을 알아내는 과정은 다음과 같습니다.

 

    LEN 함수로 전체 문자열 길이를 알아낸다.

    찾을 문자를 빼고 나머지 문자열의 길이를 알아낸다.

    위의 두 가지 문자열 길이를 뺀다.

 

찾고 있는 문자를 빼고 측정한 길이 값을 전체 길이에서 뺀 나머지가 문자 개수 입니다. 먼저 하나의 문자를 찾는 방법입니다. 2 번에 해당하는 값을 구하기 위해 찾을 문자를 “” 공백으로 바꿉니다. 함수는 문자를 교체하는 SUBSTITUTE 입니다.

엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

 


찾고 있는 문자를 모두 공백으로 바꾸고 난 나머지 길이 값을 LEN 함수로 계산합니다

엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

 

이제 소문자 p 에 대한 개수를 세기 위해 문자열 전체 길이값에서 뺍니다. 수식은 다음과 같습니다.

 

=LEN(B2) - LEN(SUBSTITUTE(B2, "p", ""))

엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

 

SUBSTITUTE 는 대소문자를 구분하기 때문에 따로 구할 수 있습니다.

 

=LEN(B2) - LEN(SUBSTITUTE(B2, "P", ""))

엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

 

2. 문자열 세는 방법

 

두 번째는 하나의 문자가 아닌 문자열의 개수를 세는 방법입니다. 계산 순서는 같지만 구한 값에서 찾을 문자열 길이 값으로 나눠야 합니다. 만약 위와 같은 방식이면 문자 개수만큼 값이 나옵니다.

 

=LEN(B2) - LEN(SUBSTITUTE(B2, "Python", ""))

엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

 

전체 구한 값에서 찾을 문자열 “Python” 의 길이 값을 나눕니다. “Python” 의 길이 값이 6 이므로 정답은 2 입니다.

 

=(LEN(B2) - LEN(SUBSTITUTE(B2, "Python", ""))) / LEN("Python")

엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

 

3. 셀 전체 영역에서 동일한 문자열 개수 구하는 방법

 

마지막으로 영역에 있는 모든 텍스트를 참조해서 문자열 개수를 찾는 방법입니다. 여기서는 배열 수식을 이용해야 값을 구할 수 있습니다. 배열 수식이란 단일 값이 아닌 배열 형태의 반환값을 계산하기 위한 수식입니다. 문법은 {=수식} 형태로 “{}” 로 수식을 감싸야 합니다. 아래 수식처럼 단일 셀이 아닌 범위를 지정해서 구합니다. 만약 배열 수식으로 표기하지 않으면 제대로 된 값을 구할 수 없습니다

엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

 

제대로 된 값을 얻기 위해 전체 수식을 {} 감싸 줍니다. 배열 수식을 표현하는 단축키가 있는데 Ctrl + Shift + Enter 입니다.

 

{=SUM((LEN(B2:B4) - LEN(SUBSTITUTE(B2:B4, "Python", "")))) / LEN("Python")}

엑셀 Excel 특정 문자 세는 방법, LEN과 SUBSTITUTE 함수 사용

Posted by 녹두장군