엑셀 함수 - 문자 관련(LEN, LEFT, RIGHT, MID)

이번 강좌에서는 문자의 길이(갯수)와 반환에 대한 내용입니다. 이미 만들어진 엑셀 문서에서 주민등록번호의 뒷자리를 감춘다던지 전화번호 뒷자리만으로 이벤트 당첨자를 공개한다던지 할때 많이 사용하는 기능입니다.

사실 함수로 자동화할 필요가 없다면 이런 기능은 주로 빠른채우기를 통해 하는것이 효과적입니다. 그러나 자동화 할 필요가 있다면 역시 함수가 좋겠지요

 

LEN

텍스트 문자열의 문자 수를 반환

LEN(text)

• text - 길이를 확인하려는 문자열. 공백도 문자로 계산됨

 

주의하실 사항은 공백문자(spacebar)도 하나의 문자로 인식해서 문자 수에 포함시킨다는 점입니다.

 

그림1

그림2

그림1과 그림2에서 LEN 함수의 인수로 "안녕"이라는 문자를 입력했습니다. 그림1에서는 문자의 개수를 2개로 반환해 주었으나 그림2에서는 4개로 출력됨을 알 수 있습니다. 안녕이라는 문자 앞 뒤로 공백문자(spacebar)가 들어갔기 때문이죠. 이렇게 오타나 정렬때문에 간혹 앞뒤로 공백문자가 들어간 경우 공백을 해결해야 되는 상황이 종종 있습니다. 이런 경우는 TRIM 함수를 같이 사용되면 해결됩니다만 TRIM은 다음에 다시 다루도록 하겠습니다.

간단하게 수식만 알아보면

=LEN(TRIM(" 안녕 "))

로 작성하면 2로 반환됩니다. 지금은 LEN 함수의 인수 text에는 공백도 포함된다는 것을 꼭 기억하시기 바랍니다.

 

728x90

 

LEFT

텍스트 문자열의 첫 번째 문자부터 시작하여 지정한 문자 수만큼 문자를 반환

LEFT(text, [num_chars])

• text - 추출하려는 문자가 들어 있는 텍스트 문자열
• [num_chars] - 추출할 문자 수.
  생략시 1로 간주해 첫 글자만 반환. 추출한 문자수보다 크다면 텍스트 전체 반환.

RIGHT

텍스트 문자열의 마지막 문자부터 지정된 개수의 문자를 반환

RIGHT(text,[num_chars])

• text - 추출하려는 문자가 들어 있는 텍스트 문자열
• [num_chars] - 추출할 문자 수.
  생략시 1로 간주해 마지막 글자만 반환. 추출한 문자수보다 크다면 텍스트 전체 반환

 

LEFT함수와 RIGHT 함수의 인수는 모두 동일합니다. 의미처럼 방향만이 다른 것이죠 

예제를 통해 확인해 보겠습니다.

 

1. G5셀 선택
2. 수식입력줄에 =LEFT( 작성 => =LEFT(
3. 인수 text에 해당하는 C5셀 선택 => =LEFT(C5
4. 콤마 입력후 이름에 성에 해당하는 개수 1입력 => =LEFT(C5,1
5. enter 입력 => =LEFT(C5,1)

성이 반환되고 G5셀을 G12까지 자동채우기해서 마무리합니다.

 

그런데 문제가 있네요 C11셀을 보시면 직원명이 남궁철수로 성이 2글자 입니다. 우리가 반환한 것은 1글자만 반환해서 "남"만 반환되었습니다. 이런 경우에는 IF함수와 LEN함수를 함께 사용하면 2글자 성을 반환할 수 있습니다.

이름이 4글자라면 앞에서 부터 2글자를 반환하고 아니면 1글자를 반환하는 수식을 만들면 되겠죠..

 

그래서 다시 수식입력줄에서 G5셀을 선택하고

=IF(LEN(C5)>3,LEFT(C5,2),LEFT(C5,1))

로 수정합니다. 입력시 나오는 힌트를 탭키를 이용해 선택하면 수월하게 수정하실 수 있습니다. 작성후 다시 G5셀을 G12까지 자동채우기해서 마무리합니다. "남궁"으로 반영되었습니다.

 

주민번호(보안)은 앞자리만 표시하고 뒷자리는 *******로 표시하고 싶습니다. 현재 목록표에서 주민등록번호 앞자리 6개와 "-"까지 총 7자리를 LEFT함수로 반환하고 나머지는 *******로 합치면 되겠네요

 

1. H5셀 선택
2. 수식입력줄에 =LEFT( 작성 => =LEFT(
3. text 인수에 해당하는 D5셀 선택 => =LEFT(D5
4. 콤마 입력 후 7([num_chars]) 입력 후 함수를 끝내기위해 닫힘괄호를 입력 => =LEFT(D5,7)
5. 문자를 합치기 위해 &기호 입력후 "*******"를 입력 => =LEFT(D5,7)&"*******"

원하는 형태로 주민번호가 반환되고 H12까지 자동채우기해서 마무리합니다.

 

 

주민번호 작성이 완료되었습니다. 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

 

 

사번은 뒤 3자리 숫자만 나오게 하고 싶습니다. 이제 RIGHT함수를 사용해야 겠네요

 

1. I5셀 선택
2. 수식입력줄에 =RIGHT( 작성 => =RIGHT(
3. text 인수에 해당하는 B5셀 선택 => =RIGHT(B5
4. 콤마 입력후 3([num_chars]) 입력 => =RIGHT(B5,3
5. enter 입력 => =RIGHT(B5,3)

사번의 숫자가 반환되고 I5셀을 I12까지 자동채우기해서 마무리합니다.

 

이제 성별(숫자)를 반환하고 싶습니다. 성별(숫자)는 주문등록번호 뒷자리 첫글자입니다. 그런데 난관에 봉착했습니다. LEFT나 RIGHT함수는 문자의 처음이나 끝에서부터 반환하는 함수이기 때문에 중간에 글자만을 반환할 수 없습니다. RIGHT함수로 7글자를 반환하면 뒷자리 모두가 반환 될 것이고 LEFT도 처음부터 반환할 테니까요.

이 경우에 사용하는 함수가 바로 MID 입니다.

 

반응형

 

MID

텍스트 문자열에서 지정된 위치로부터 지정된 수만큼 문자를 반환

MID(text, start_num, num_chars)

• text - 추출하려는 문자가 들어 있는 텍스트 문자열
• start_num - 텍스트에서 추출할 첫 문자의 위치
• num_chars - 추출할 문자 수

 

MID도 LEFT나 RIGHT로 거의 유사합니다. 인수로 text와 num_chars를 가지고 있습니다. 다른점이라면 start_num이 존재하는 것이죠 start_num은 좌측에서부터 반환하고 싶은 문자의 위치(문자수) 입니다. 그러니까 위의 예제에서 반환하고자하는 숫자는  좌측에서 부터 8번째에 위치하고 있습니다. start_num이 8이 되는 것이죠. 그리고 한글자만 반한할 것이니까 num_chars는 1이 되겠죠.

주의 할 점도 있습니다. LEFT나 RIGHT의 경우 num_chars는 선택요소로 생략하면 1글자만 반환하지만 MID함수는 반드시 사용해야하는 필수 요소입니다. 1글자라도 반드시 1을 명시해줘야 합니다.

 

1. J5셀 선택
2. 수식입력줄에 =MID( 작성 => =MID(
3. text 인수에 해당하는 D5셀 선택 => =MID(D5
4. 콤마 입력후 start_num에 해당하는 8 입력 => =MID(D5,8
5. 콤마 입력후 1(num_chars) 입력 => =MID(D5,8,1
6. enter 입력 => =MID(D5,8,1)

이후 J5셀은 J12까지 자동채우기해서 마무리합니다.

 

 

텍스트 함수(LEFT, RIGHT, MID, LEN).xlsx

0.01MB