116_엑셀 TEXT, REPT, LEN 함수 – 함수 중급 (43)

엑셀에서 숫자 앞에 0을 표시하는 방법은 참 많습니다. 핸드폰 번호 같은 경우는 엑셀의 셀 서식 특수 기호에 가면 있지만 시리얼 코드 라던지 숫자 앞에 0을 붙여야 할 경우를 모르는 분들이 의외로 많은데요, 제가 자주 쓰는 방법을 소개해 드리겠습니다. 그러나 제가 소개하는 방법 말고도 다른 방법이나 함수의 조합은 무궁 무진한 것을 강조 합니다.

116연습파일.xlsx

116연습파일_완성본.xlsx

  1. 셀 서식 변경

    사실 이 방법이 제일 간단한 것이지요. 엑셀의 기본 셀 서식은 일반 서식으로 유효 숫자만 표시하게 되어 있습니다. 즉 숫자 앞에 붙은 0은 유효숫자가 아니므로 표시 되지 않죠. 그 기본 서식만 바꿔 주면 됩니다.

     

    숫자 앞에 0 을 채워서 6자리를 만들고 싶습니다.

     

     

    열을 선택한 후 셀 서식을 들어 갑니다.

     

     

    사용자 지정을 클릭 하시고 000000을 지정 합니다.

     

    여기서 제가 와일드 카드를 전부 설명 드리는 것은 무리이고 저도 적용할 때 마다 매번 까먹어서 찾을 때가 많습니다. 그런데 여기서 0의 의미는 0이 아니라 0을 포함한 유효숫자 6자리를 표시한다는 뜻입니다.

     

     

    셀 서식으로 간단히 해결 되었습니다.

     

     

  2. TEXT 함수

    셀 서식을 바꾸지 않고 함수로 위의 원하는 결과를 얻을 수 있는데 사실 엑셀이 해석하는 원리는 같습니다. 셀 서식을 바꿔서 보여주는 것으로 서식 변경과 같습니다.

    함수 사용법

    TEXT(셀, 셀서식) 은 해당 셀을 셀 서식으로 바꿔 줍니다.

     

    B2셀을 6자리 0을 포함한 유효 숫자로 바꿔줍니다.

     

     

  3. 숫자에 문자가 포함된 경우

    "000000" 셀 서식 지정은 숫자에만 적용 되지 만약 중간에 문자 하나라도 끼어 있게 된다면 적용이 되질 않습니다. 문자가 있는 숫자는 무조건 문자이기 때문입니다.

     

    이렇게 문자가 있는 셀을 "000000"로 셀 서식을 변경해 보겠습니다.

     

     

    숫자만 변경이 되고 문자가 있는 셀은 변경이 되질 않았습니다.

     

    이걸 해결 하는 방법은 많습니다만 전 서식 창을 이용하기 보다 주로 CONCATENATION (셀이나 내용 합치기) 을 함수와 함께 이용합니다. CONCATENATION을 정확히 한 단어 한국어로 머라 하는지 아는 분은 좀 댓글로 알려 주세요. 사전적 의미는 아닐 거에요.

     

  4. LEN 함수

    LEN 은 LENGTH (길이)의 줄임으로 해당 셀의 문자 개수를 세어 줍니다.

     

    함수 사용법은 생략 할게요. 인수 하나만 받고 문자 개수를 리턴 합니다.

     

     

    757은 세 글자 44622SMS 5 글자 이지요. 쉽죠?

     

     

  5. REPT 함수

    함수 사용법

    REPT(반복할문자,반복횟수) 는 문자를 일정 횟수만큼 반복합니다.

    REPT 함수는 REPEAT (반복) 의 줄임 말로 특정 문자를 인수로 받아서 입력 받은 인수 횟수만큼 반복합니다.

     

    지금 *를 입력 받아서 L열에 있는 숫자 만큼 반복하고 있습니다. 왜 절대 참조인지는 아시겠죠? 모르시면 이전 포스팅을 참조하세요.

     

     

    $ 사인으로 바꾸면 $를 반복 합니다.

     

     

     

  6. LEN, REPT 그리고 CONCATENATION (셀이나 내용 합치기)

    이 LEN 함수 REPT함수와 CONCATENATION을 이용해 문자가 포함된 숫자의 앞에 6자리를 채우도록 0을 붙여 보겠습니다.

     

    REPT로 0을 필요한 횟수만큼 반족하고 &를 이용해 원래 값을 갔다 붙입니다.

     

    6-LEN 함수는 6자리에서 0이 필요한 자리를 계산 합니다. 즉 원래 값이 3자리라면 6-3이 되므로 0이 3개가 필요합니다.

    REPT함수는 6-LEN으로 구한 숫자만큼 0을 반복 합니다.

    & 는 CONCATENATION으로 000뒤에 원래 숫자 757을 그냥 갔다 붙입니다.

     

    결과 입니다.

    특별한 숫자 표시 형식이 필요 하세요? 사실 CONCATENATION 문자열 함수 개면 표현 못할 형식이 없습니다.


  1. 2016.01.09 09:46

    비밀댓글입니다

    • 스마트제이킴 2016.01.09 09:52 신고

      맞아요. ㅋ 미국사람도 많이 하는 실수라 구글에도 잘못된 스펠링이 검색되네요.

  2. 성단 2018.05.15 12:46 신고

    혹시 입력값이 없을 때는 -를 삽입하고 싶은데 그건 어떻게 적용하면 될까요?

+ Recent posts