엑셀에서 특정 문자 개수 세기 – 가장 정확한 공식
엑셀을 사용하다 보면 단순 계산보다 텍스트 안에 특정 문자가 몇 개 들어 있는지를 확인해야 하는 경우가 자주 발생합니다.
- 이메일 주소에 @가 정확히 1개만 있는지 확인
- 전화번호에 하이픈(-)이 몇 개 포함되어 있는지 체크
- 문장 안에 특정 키워드가 몇 번 등장하는지 분석
- 데이터에 불필요한 특수문자가 얼마나 섞여 있는지 점검
이럴 때 가장 안정적이고 많이 사용되는 방법이 바로 LEN + SUBSTITUTE 공식입니다.
1. 기본 개념부터 이해하기
이 공식의 핵심 원리는 매우 단순합니다.
- ① 전체 문자열의 길이를 구한다
- ② 특정 문자를 제거한 문자열의 길이를 구한다
- ③ 두 길이의 차이를 계산한다
이 차이값이 바로 해당 문자의 개수가 됩니다.
2. 기본 공식 구조
=LEN(전체텍스트) - LEN(SUBSTITUTE(전체텍스트, "찾을문자", ""))아주 간단하지만, 응용 범위는 매우 넓습니다.
3. 가장 기본적인 예제
① @ 문자 개수 세기
A1 = "hero@naver.com"
=LEN(A1) - LEN(SUBSTITUTE(A1,"@",""))결과 → 1
이 공식은 이메일 형식 검증에 자주 사용됩니다.
② 하이픈(-) 개수 세기
A1 = "010-1234-5678"
=LEN(A1) - LEN(SUBSTITUTE(A1,"-",""))결과 → 2
4. 실무에서 자주 쓰는 활용 사례
① 이메일 주소 유효성 간단 검증
@가 정확히 1개만 있어야 정상 이메일입니다.
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=1,"정상","확인필요")② 전화번호 형식 체크
하이픈이 2개가 아니라면 잘못된 입력일 가능성이 큽니다.
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,"형식OK","오류")③ 문장 속 쉼표 개수 파악
CSV 데이터나 주소 데이터에서 매우 유용합니다.
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))5. 특정 단어(문자열) 개수 세기
SUBSTITUTE는 문자뿐 아니라 단어(문자열)도 처리할 수 있습니다.
예제
A1 = "엑셀은 쉽고 엑셀은 강력하다"
=(LEN(A1)-LEN(SUBSTITUTE(A1,"엑셀","")))/LEN("엑셀")결과 → 2
문자열 길이만큼 나누는 것이 핵심 포인트입니다.
6. 대소문자 구분 없이 세고 싶다면?
SUBSTITUTE는 대소문자를 구분합니다. 구분 없이 세고 싶다면 LOWER 또는 UPPER와 함께 사용합니다.
=LEN(LOWER(A1))-LEN(SUBSTITUTE(LOWER(A1),"excel",""))7. 공백 개수 세기 (의외로 매우 중요)
데이터 오류의 상당수는 불필요한 공백 때문에 발생합니다.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))이 공식으로 공백이 많은 셀을 빠르게 찾아낼 수 있습니다.
8. IF 함수와 결합한 조건 판단
① 특정 문자 포함 여부 판단
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))>0,"포함","미포함")② 특정 문자 과다 사용 체크
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"!",""))>=3,"과다","정상")마케팅 문구, 리뷰 데이터 분석에 매우 유용합니다.
9. LEN + SUBSTITUTE 공식의 장점
- 보조 열 없이 하나의 수식으로 처리 가능
- 모든 엑셀 버전에서 사용 가능
- 문자, 기호, 단어까지 확장 가능
- 속도와 안정성이 뛰어남
정리
LEN + SUBSTITUTE 조합은 엑셀 텍스트 분석에서 반드시 알아야 할 실무 핵심 공식 중 하나입니다.
문자 개수 파악은 단순한 계산이 아니라 데이터 검증, 오류 탐지, 자동 분기 처리의 출발점입니다.
다음 글에서는 이 공식을 확장한 TEXTSPLIT / TEXTBEFORE / TEXTAFTER – 최신 엑셀 텍스트 분리 함수 또는 IF + 텍스트 함수로 자동 분류하기 중 하나로 이어가면 매우 좋은 흐름입니다.
0 댓글