엑셀 파일에 있는 데이터를 분류할 때 셀 값이 영어와 숫자로만 구성되어 있는지, 한글이 포함되어 있는지 알고 싶을때가 있다. 또 첫글자가 영어로 시작하는지 한글로 시작하는지 구분하는 방법도 알아본다.
함수 설명
이 작업을 하기 위해 필요한 함수는 LEN 과 LENB 이다.
LEN 함수 : 어떤 셀 값이 텍스트 문자열일 때, 그 문자 수를 반환
LENB 함수 : 문자열을 나타내는 총 바이트 수를 반환
✔️ 두 함수 모두 인수로는 문자열이 와야 한다. 즉 LEN(text) 형태로 입력되어야 정상 작동한다.
✔️ 텍스트 문자열 중간에 공백이 끼어 있어도 문자 수 1개와 1Byte로 계산된다.
알다시피 한글은 한글자당 2Byte, 영어와 숫자는 한글자에 1Byte를 사용한다. 여기까지 보았다면 감을 잡은 분들이 있을지도 모르겠다.
한글 포함여부 확인방법
문자열의 길이와 Byte 수를 비교하여 한글이 끼어있는지 확인할 수 있다.
IF문을 이용해 다음과 같이 작성하여 구분한다.
=IF(LEN(text)=LENB(text),”영어”,”한글”)
IF 함수 설명
IF(조건,값1,값2)
조건 : 비교하고자 하는 논리 수식
값1 : 조건이 참(TRUE)일 경우 반환하는 값
값2 : 조건이 거짓(FALSE)일 경우 반환하는 값
여기서 조건은 LEN 함수로 셀 값의 문자 수와 LENB 함수로 셀 값의 Byte 수가 같은지 비교한다. 같다면 숫자 또는 영어로만 구성되어 있다는 뜻이고, 다르다면 한글이 끼어 있어서 Byte 값이 더 크게 나왔다는 의미이다.
예시를 통해 살펴보자.
| 셀 값 | LEN | LENB | 수식 결과 |
| 아파트 | 3 | 6 | 한글 |
| apartment | 9 | 9 | 영어 |
| 아파트apart | 8 | 11 | 한글 |
| 123apart | 8 | 8 | 영어 |
| 아파트 abc | 7 | 10 | 한글 |
이렇게 셀 값에 한글이 포함되어 있는지, 숫자/영어/공백 으로만 이루어져 있는지 확인할 수 있다.
첫글자 한글 영어 확인방법
다음으로 첫글자 시작이 한글인지 영어인지 확인하는 방법이다. 마찬가지로 Byte 수가 다른 특성을 이용해서 간단한 수식을 만들 수 있다.
=LENB(LEFT(text,1))
LEFT 함수 설명
LEFT(text, Num_chars)
text : 추출하려는 문자가 있는 위치
Num_chars : 왼쪽에서부터 추출할 문자 수
LEFT 함수는 어떤 셀 값의 왼쪽부터 잘라서 불러오는데, 여기에 Num_chars 인수값으로 1을 넣거나 생략하면 맨 왼쪽의 첫 글자만 반환하게 된다. 여기에 LENB 함수를 씌워서 그 첫글자의 Byte 값이 1인지 2인지 판별하면 된다.
| 셀 값 | 수식 결과 | 첫글자 |
| 아파트 | 2 | 한글 |
| abc아파트 | 1 | 영어 |
| apartment | 1 | 영어 |
| 123동 아파트 | 1 | 영어 |
(영어라고 표시했지만 영어 또는 숫자로 구성된, 한글이 아닌 1Byte 문자라는 의미이다.)
이렇게 문자열 개수를 반환해주는 LEN 함수와 문자의 Byte 수를 체크해주는 LENB 함수를 이용해서, 엑셀의 셀 값이 한글인지 영어인지 구분할 수 있다. 이걸 응용해서 값이 모두 한글로만 되어 있는지를 체크하고 싶다면 LENB 값이 LEN의 딱 두배가 되는지를 보면 된다.