엑셀 글자수 세기, 엑셀 전체 글자수 세기 (LEN 함수, SUBSTITUTE, SUMPRODUCT 활용)

글자 수 세기는 엑셀 작업을 하면서 가끔 필요할 때가 있습니다. 글자 수가 일정해야 되는 상황에서 오류가 발생하여 해당 셀을 찾아 조정을 해야 할 때, 한 셀의 글자 수뿐만 아니라 영역의 글자 수를 세어 일정한 숫자로 조정이 필요한 경우 등 해당 글자 수 세기는 하나의 개별 셀뿐만 아니라 지정된 영역의 글자 수도 세어야 하는 경우가 발생합니다.

엑셀 글자 수 세기

글자 수 세기는 엑셀 셀간의 글자 수 세기와 엑셀 전체 및 부분 영역의 글자 수 세기로 나눌 수 있습니다. 

엑셀 부분 셀 글자 수 세기

엑셀 셀 안의 글자 수 세기는 많이 알려져 있는 LEN 함수로 실시합니다.

LEN 함수 정의

LEN 함수는 엑셀의 편집의 핵심인 문자 함수 중에 하나입니다. RIGHT, LEFT, MID 함수 등과 연결하여 IF함수의 조건과 함께 사용한다면 많은 응용이 가능한 함수입니다. 원천적인 기능은 문자열의 길이를 숫자로 변경하여 알려 줍니다. 문자열의 길이를 알려 주기 때문에 띄어쓰기까지 1의 숫자로 센 다는 점이 아쉬운 함수 입니다만, 해당 아쉬운 점을 상쇄할 함수는 존재하기 때문에 조합하여 사용하시면 됩니다. 

LEN 함수 구문

 = LEN(셀) or LEN("문자")

A1 셀에 "우리 지금 만나"라는 문장이 있다고 한다면, LEN(A1)은 8이라는 문자열의 길이를 숫자로 보여줍니다. 

8의 숫자는 글자 6개와 중간의 띄어쓰기 2개를 의미합니다. 

 

SUBSTITUTE 함수 정의

SUBSTITUTE 함수는 위의 LEN함수의 띄어쓰기 숫자 세기를 제거해 줄 활용도가 높은 텍스트 함수입니다. 

기본 기능은 원본 문자열에서 특정 문자를 찾은 후 변경할 수 있다는 것입니다. 

 

SUBSTITUTE 함수 구문

=SUBSTITUTE(원본 문자열 셀, 찾을 문자, 바꿀 문자)

 

엑셀 부분 셀 글자수만 세기(띄어쓰기 제외)

LEN함수와 SUBSTITUTE함수를 조합하면 A1의 셀의 "우리 지금 만나"는 6의 숫자로 보입니다. 

=LEN(SUBSTITUTE(A1," ",""))

LEN의 함수로는 문자열의 길이를 세며, SUBSTITUTE는 띄어쓰기 " "를 삭제 "" 해 주어 6의 라는 문자열의 길이를 보여줍니다.

엑셀 전체 글자 수 세기

엑셀 전체 글자수 세는 방법은 위의 셀 문자열의 길이를 구하는 것과는 조금 차원이 다릅니다.

SUMPRODUCT 함수와 LEN함수, SUBSTITUTE함수를 조합하여 사용합니다. 

아래는 SUMPRODUCT함수를 처음 접하시는 분들을 위한 추가 서명입니다. 

엑셀 SUMPRODUCT 함수 사용하기(SUMIFS, COUNTIFS 대체 함수)

SUMPRODUCT, LEN, SUBSTITUTE 활용

A1부터 A10까지 세 글자로 된 이름이 적혀 있으며, 각각의 이름은 띄어쓰기가 되어 있다면 해보겠습니다. 

위의 LEN 함수 구문과 같이 LEN은 하나의 셀의 문자열의 길이를 보여 주었습니다. 하지만 SUMPRODUCT와 함께 사용한다면 지정한 범위의 전체 문자열의 길이를 숫자로 보여줍니다. 

 

A1 : 홍 길 동

A2 : 이 순 신

A3 : 김 삿 갓

.

.

A10 : 김 영 철

 

엑셀 특정 영역 글자 수 세기 

=SUMPRODUCT(LEN(A1:A10))

A1부터 A10의 총문자열의 길이는 50이라는 숫자로 위의 함수 구문으로 표현 되게 됩니다. 

하나의 셀을 3개의 글자와 2개의 띄어쓰기로 총 10개의 셀의 문자열을 구했기 때문입니다. 

 

엑셀 특정 영역 띄어쓰기 및 빈칸 제외 글자 수 세기

=SUMPRODUCT(LEN(SUBSTITUTE(A1:A10," ","")))

A1부터 A10의 총문자열의 길이는 30이라는 숫자로 위의 함수 구문으로 표현 되게 됩니다. 

하나의 셀에 2개의 띄어쓰기(빈칸)를 삭제하여 문자열의 길이를 글자수 3개로 동일하게 수정 후 문자열의 길이의 총합을 구했기 때문입니다.

 

엑셀 특정 영역 빈칸 개수 세기

=SUMPRODUCT(LEN(A1:A10) - LEN(SUBSTITUTE(A1:A10," ","")))

A1부터 A10의 총빈칸의 개수는 20이라는 숫자로 위의 함수 구문으로 표현 되게 됩니다. 

50과 30의 빼기이기 때문에 당연한 숫자입니다. 

 

SUMPRODUCT을 위에서 활용한 방법은 SUM 함수, 즉 더하기 함수와 정확히 일치합니다. 

하지만 SUMPRODUCT로 설명드리는 이유는 엑셀 버전마다 더하기 함수인 SUM의 기능을 100% 활용 못하는 경우도 있기 때문입니다. 또한, SUMPRODUCT의 더하기 기능과 뺄셈 기능에 대해 익숙해지실 수 있는 활용 방법 중 하나가 되기 때문에, 위와 같은 방식으로 설명드렸습니다.