엑셀 텍스트 함수
IT위키
더 많은 작업
Excel Text Functions
문자열을 자르고 붙이고 찾고 바꾸는 엑셀 함수들
- =LEFT(문자열, 개수) : 왼쪽에서 지정한 개수만큼
- =RIGHT(문자열, 개수) : 오른쪽에서 지정한 개수만큼
- =MID(문자열, 시작위치, 개수) : 시작위치부터 지정한 개수만큼
- 개수를 생략하면 LEFT·RIGHT 는 한 글자를 돌려준다.
- =LEN(문자열) : 글자 수. 공백도 한 글자로 센다
- =FIND(찾을문자열, 대상, 시작위치) : 위치를 돌려준다. 대소문자를 구분하고 와일드카드를 쓸 수 없다
- =SEARCH(찾을문자열, 대상, 시작위치) : 위치를 돌려준다. 대소문자를 구분하지 않고 와일드카드를 쓸 수 있다
- 찾지 못하면 둘 다 #VALUE! 오류를 낸다.
- 엑셀은 한글 한 글자도 1로 센다. 바이트로 세려면 LENB·FINDB·MIDB 를 쓴다.
- SEARCHB·FINDB·LENB 는 한글 한 글자를 2바이트, 영문·숫자·공백을 1바이트로 센 위치·길이를 돌려준다(한국어처럼 2바이트 문자를 쓰는 언어 설정일 때). MID·REPLACE·LEFT 는 글자 단위로 센다
- 찾을 문자열이 빈 셀이면 SEARCH·SEARCHB 는 1 을 돌려준다
- REPLACE 의 시작 위치가 글자 수보다 크면 새 문자열이 끝에 덧붙는다
예: A1 빈 셀, A2 "한국 대한민국", A3 "분기 수익", A4 "수익", A5 "아름다운 설악산"
| 수식 | 계산 | 결과 |
|---|---|---|
| =MID(A5,SEARCHB(A1,A5)+5,3) | 빈 문자열을 찾으면 1, MID(A5,6,3) | 설악산 |
| =REPLACE(A5,SEARCHB("한",A2),5,"") | SEARCHB 는 1, 앞 5글자 "아름다운 "(공백 포함)를 지움 | 설악산 |
| =MID(A2,SEARCHB(A4,A3),2) | "분기 수익" 에서 "수익" 은 분(2)+기(2)+공백(1) 다음인 6바이트째, MID(A2,6,2) | 민국 |
| =REPLACE(A3,SEARCHB(A4,A3),2,"명세서") | REPLACE(A3,6,2,"명세서") 인데 A3 는 5글자라 끝에 덧붙음 | 분기 수익명세서 |
- 마지막 식을 "분기명세서" 로 보면 틀린다. SEARCHB 가 돌려준 6 은 바이트 위치인데 REPLACE 는 그것을 글자 위치로 쓰기 때문이다
- =REPLACE(문자열, 시작위치, 개수, 새문자열) : 위치로 지정해 바꾼다
- =SUBSTITUTE(문자열, 찾을문자열, 새문자열, 몇번째) : 내용으로 찾아 바꾼다
- =CONCAT(문자열1, ...) 또는 & 연산자 : 이어 붙인다
- =TEXTJOIN(구분자, 빈셀무시, 문자열1, ...) : 구분자를 넣어 이어 붙인다
- =TRIM(문자열) : 앞뒤 공백을 없애고 중간 공백은 하나만 남긴다
- =REPT(문자열, 반복횟수) : 문자열을 지정한 횟수만큼 반복해 이어 붙인다. 인수 순서는 문자열이 먼저, 횟수가 나중이다
- 반복횟수가 0 이면 빈 문자열, 소수면 정수 부분만 쓴다. 결과가 32,767자를 넘으면 #VALUE! 오류가 난다
- 셀 안에 막대처럼 개수를 그릴 때 쓴다. 예: =REPT("■", B3/10) - B3 값 10마다 ■ 하나
- 개수를 함수로 구해 넣을 수 있다. 학점 범위[B3:B10]에서 D3 의 학점이 나온 횟수만큼 ♣ 를 표시하려면 =REPT("♣", COUNTIF($B$3:$B$10, D3)) 로 쓴다. COUNTIF 도 범위가 먼저, 조건이 나중이다 → 엑셀 함수 COUNTIF
- =UPPER, =LOWER : 모두 대문자·소문자로
- =PROPER : 단어마다 첫 글자만 대문자로
- =TEXT(값, 표시형식) : 숫자를 지정한 표시 형식의 문자열로
- =VALUE(문자열) : 숫자 모양의 문자열을 숫자로
- LEFT·MID·RIGHT 가 돌려주는 값은 숫자처럼 보여도 텍스트라 SUM 으로 더하면 0 이다. VALUE 로 바꾸거나
--·*1연산을 거쳐 계산한다 → 엑셀 수학 함수
A1 셀이 "대한민국만세" 라면 아래 셋은 모두 "민국만세" 를 돌려준다.
- =MID(A1, FIND("대", A1)+2, 5)
- =MID(A1, SEARCH("대", A1)+2, 5)
- =RIGHT(A1, LEN(A1)-2)
반면 =RIGHT(A1, FIND("대",A1)+5) 는 오른쪽에서 6글자를 가져오므로 결과가 다르다. RIGHT·LEFT 의 두 번째 인수는 위치가 아니라 개수라는 점이 함정으로 자주 나온다.