LEN · FIND · SEARCH로 문자열 분석 끝내기 (위치·개수·N번째 찾기 1분 컷)
이메일에서 도메인만 뽑고, 파일명에서 확장자만, 제품코드의 N번째 구분자 위치까지—LEN·FIND·SEARCH 조합이면 다 됩니다.
기본 개념 & 차이
| 함수 | 형식 | 특징 |
|---|---|---|
| LEN | =LEN(text) | 문자 개수(공백 포함)를 반환 |
| FIND | =FIND(find_text, within_text, [start_num]) | 대소문자 구분, 와일드카드 미지원 |
| SEARCH | =SEARCH(find_text, within_text, [start_num]) | 대소문자 무시, * ? 와일드카드 지원 |
길이 세기 – LEN
예제 1) 공백 포함 길이
=LEN(A2)예제 2) 앞뒤 공백 제외
=LEN(TRIM(A2))TIP: NBSP(CHAR(160))는 TRIM으로 지워지지 않을 수 있어 SUBSTITUTE(A2,CHAR(160)," ") 후 LEN을 쓰면 정확합니다.
위치 찾기 – FIND vs SEARCH
예제 3) 이메일에서 @ 위치
=FIND("@", A2)예제 4) 대소문자 무시 포함 여부
=ISNUMBER(SEARCH("kim", A2)) // 포함이면 TRUE예제 5) 와일드카드
=ISNUMBER(SEARCH("*.xlsx", A2)) // 확장자가 .xlsx인 파일명N번째 발생 위치 & 마지막 구분자
예제 6) N번째 하이픈 위치
=FIND("#", SUBSTITUTE(A2, "-", "#", N))구분자가 N개 미만이면 오류 → IFERROR로 처리:
=IFERROR(FIND("#",SUBSTITUTE(A2,"-","#",N)),0)예제 7) 마지막 구분자 위치
=FIND("|", SUBSTITUTE(A2,"-","|", LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))예제 8) 365 간단 버전 (끝에서 찾기)
=TEXTAFTER(A2,"-", -1) // 마지막 '-' 뒤 텍스트
=TEXTBEFORE(A2,"-", -1) // 마지막 '-' 앞 텍스트포함 개수 세기
예제 9) 특정 문자/문자열 등장 횟수
=(LEN(A2)-LEN(SUBSTITUTE(A2, "ab", "")))/LEN("ab")대소문자 무시:
=(LEN(UPPER(A2))-LEN(SUBSTITUTE(UPPER(A2),"AB","")))/2원하는 부분 추출 (LEFT/MID/RIGHT · TEXTAFTER/BEFORE)
예제 10) 이메일 도메인
=RIGHT(A2, LEN(A2)-FIND("@",A2))
/* 365 */ =TEXTAFTER(A2,"@")예제 11) 확장자(. 뒤)
=RIGHT(A2, LEN(A2)-FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".","")))))
/* 365 */ =TEXTAFTER(A2,".",-1)예제 12) 중간 코드(두 하이픈 사이)
=MID(A2, FIND("-",A2)+1, FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)
/* 365 */ =TEXTSPLIT(A2,"-")(1,2)자주 하는 실수 & 성능 팁
- #VALUE! 오류 → 찾는 텍스트가 없을 수 있으니
IFERROR로 감싸거나 기본값 제공. - NBSP/줄바꿈 →
SUBSTITUTE(,CHAR(160)," "),SUBSTITUTE(,CHAR(10)," ")로 정리 후 검색. - 성능 → 동일한
LEN(A2)등은LET으로 재사용, 전체열 참조(X:X) 지양. - 대소문자 요구 → 엄격 비교는 FIND, 느슨한 비교는 SEARCH.
요약 정리
| 목표 | 대표 공식 |
|---|---|
| 길이 | LEN(A2), 공백 정리 후 LEN(TRIM(...)) |
| 포함 여부 | ISNUMBER(SEARCH("키워드",A2)) |
| N번째 위치 | FIND("#",SUBSTITUTE(A2,"-","#",N)) |
| 마지막 뒤 텍스트 | TEXTAFTER(A2,"-",-1) 또는 SUBSTITUTE 응용식 |
FAQ
FIND가 검색을 못 찾으면 왜 오류가 나나요?
반환형이 위치(숫자)라서 미발견 시 #VALUE!가 납니다. IFERROR(FIND(...),0)처럼 기본값을 두세요.
한글/영문 섞인 문자열에서도 LEN은 정확한가요?
네, LEN은 ‘문자 수’를 반환합니다. 바이트 기준 계산이 필요할 때만 LENB를 사용하세요.