엑셀 자료를 정리할 때 숫자 데이터 앞에 일정 개수의 0을 채워야 하는 경우가 있습니다.
셀에 숫자 데이터를 입력할 경우 00001을 입력해도 셀 서식이 텍스트가 아니라면 1로 변경되게 됩니다.
그렇다면 숫자 데이터만 입력했을 때 내가 원하는 0의 개수만큼 채워 넣으려면 어떻게 해야 할까요?
엑셀 숫자 앞에 0을 채우는 방법 3가지를 소개해드립니다.
- 셀 서식 - 사용자 지정 사용
- TEXT 함수 사용
- LEN, REPT 함수 사용
셀 서식 - 사용자 지정 사용
위와 같이 일련번호를 6자리로 표시하려면 어떻게 해야 할까요?
가장 간단한 방법은 셀 서식 - 사용자 지정을 사용하면 됩니다.
먼저 변환하고자 하는 데이터를 범위 지정한 후 우클릭 한 뒤 셀 서식 메뉴에 들어갑니다.
셀 서식에서 사용자 지정을 선택하면 위와 같이 데이터 형태를 다양한 방식으로 변경할 수 있습니다.
데이터 형식에 위와 같이 000000을 입력하면 0의 개수만큼 자릿수가 채워져 표시되는 것을 확인할 수 있습니다.
또한 위 빨간 박스의 보기 창에 데이터 형식이 어떤 식으로 변화되는지 바로 확인할 수 있습니다.
확인을 누르면 위와 같이 일련번호 데이터가 0의 개수인 6개만큼 자릿수가 채워져 표시되는 것을 확인할 수 있습니다.
TEXT 함수 사용
이번에는 함수를 사용하여 위 데이터처럼 셀 서식을 변경해보겠습니다.
TEXT(Value, Format_text)
- Value : 함수로 표시할 값
- Format_text : 값에 적용할 서식
여기서 Format_text(값에 적용할 서식) 이란 위에서 설명한 사용자 지정에서의 형식을 뜻합니다.
따라서 Format_text에 "000000"을 입력하면 됩니다.
| = TEXT(B2, "000000") |
우측에 새 셀을 생성한 뒤 TEXT 함수를 사용하여 위와 같이 입력하면 됩니다.
위와 같이 TEXT함수를 사용하여 셀 서식의 형태를 지정할 수 있습니다.
LEN, REPT 함수 사용
셀 서식을 변경하여 숫자 데이터 앞에 0을 채우는 방식은 숫자만 가능합니다.
"A7676"처럼 문자 데이터가 섞여있을 경우에는 다른 방식으로 0을 채워야 합니다.
함수 사용에 앞서 이해하기 쉽도록 위와 같이 셀을 추가하였습니다.
* LEN 함수
LEN(Text)
- Text : 길이를 확인할 셀 또는 문자열 데이터
LEN 함수는 셀 또는 문자열의 길이를 반환하는 함수로써 먼저 위 일련번호가 몇 자리인지 확인해보겠습니다.
| = LEN(B2) |
위와 같이 함수에 길이를 확인할 셀을 입력하면 됩니다.
위와 같이 각 일련번호의 데이터 길이가 표시되었습니다.
* REPT 함수
REPT(Text, Number_times)
- Text : 반복할 텍스트
- Number_times : 반복 횟수
REPT 함수는 특정 텍스트를 반복하여 표시해주는 함수로써 위 함수를 사용하여 표시하려는 0의 개수에서 LEN의 개수를 빼어
표시해보겠습니다. (6 - LEN)
| = REPT("0", 6-C2) |
먼저 반복할 텍스트에는 "0"을 입력하고
반복 횟수에는 내가 표시하려는 자릿수(6)에서 앞서 계산한 LEN을 뺀 값을 입력하면 됩니다.
위와 같이 표시하려는 0의 자릿수에서 LEN을 뺀 만큼 표시되게 됩니다.
그렇다면 이제 표시한 0과 일련번호를 합치면 되겠죠?
CONCATENATE 함수를 사용하여 합쳐보겠습니다.
* CONCATENATE 함수
CONCATENATE(TEXT 1, TEXT 2....)
Text : 합칠 텍스트
| = CONCATENATE(D2, B2) |
매우 간단합니다.
0이 앞에 붙어야 하므로 TEXT1에는 REPEAT 값을 넣고 TEXT2에는 일련번호 값을 넣습니다.
위와 같이 문자열이 포함된 데이터도 LEN, REPT, CONCATENATE 함수를 사용하여 앞에 0을 채워 넣을 수 있습니다.
알아두면 유용한 엑셀 지식iN
2020/11/13 - [엑셀 지식iN] 엑셀 매크로 기능 사용하는 방법은?
2020/11/06 - [엑셀 지식iN] 엑셀의 셀 내용을 합치는 방법은?
2020/11/05 - [엑셀 지식iN] 엑셀 빈 셀 자동으로 채우는 방법은?
최근댓글