앞자리 0(leading zero)은 주로 숫자의 자릿수를 일정하게 유지하기 위해 사용됩니다. 고유 식별번호, 우편번호, 보안번호 같은 기록을 관리할 때는 반드시 앞자리 0을 유지해야 합니다. 그런데 엑셀에서 푸에르토리코의 우편번호 '00901'처럼 입력하면, 엑셀은 이를 즉시 '901'로 잘라내 버립니다. 이는 엑셀이 입력값을 숫자로 인식하기 때문인데, 이 문제를 해결하려면 숫자를 텍스트로 변환해야 합니다.
이 글에서는 엑셀에서 숫자를 앞자리 0이 포함된 텍스트로 변환하는 다양한 방법을 소개합니다.
엑셀에서 숫자를 앞자리 0이 있는 텍스트로 변환하는 9가지 방법
먼저 예제 데이터를 살펴보겠습니다. 아래 데이터에는 앞자리 0이 없는 우편번호 9개가 있으며, 우편번호는 총 5자리로 구성됩니다. 이제 앞자리 0을 추가해야 하는데, 다음의 효과적인 방법들을 활용할 수 있습니다.
1. 텍스트 서식 활용하기
가장 간단한 방법입니다. 셀 서식을 숫자에서 텍스트로 변경하면 엑셀이 입력값을 실제 숫자로 취급하지 않기 때문에, 0이 사라지지 않습니다.
방법
- 서식을 적용할 빈 셀 또는 셀 범위를 선택합니다.
- 홈 탭의 숫자 그룹에서 서식 드롭다운 메뉴를 클릭합니다.
- 목록에서 텍스트 옵션을 선택합니다.
이제 해당 셀들은 앞자리 0을 표시할 준비가 되었습니다. 우편번호를 직접 입력하면 아래와 같은 결과를 얻을 수 있습니다.
2. 사용자 지정 서식 활용하기
사용자 지정 서식(Custom Format)은 셀 서식 대화상자에서 다양한 옵션을 설정할 수 있는 특수 서식입니다. 이 중 하나를 활용해 보겠습니다.
방법
- 우편번호를 새로운 셀 범위에 복사한 뒤 해당 범위를 선택합니다.
- 숫자 명령을 클릭합니다.
- 셀 서식에서 사용자 지정 옵션을 선택합니다.
- 우편번호가 5자리이므로 '00000'을 입력합니다.
- 확인을 누릅니다.
그러면 다음과 같은 결과를 얻을 수 있습니다.
3. 숫자 앞에 아포스트로피 추가하기
아포스트로피(')를 추가하는 것은 숫자 앞의 0을 유지하는 가장 쉬운 방법입니다. 가장 큰 장점은 아포스트로피가 수식 입력줄에만 표시되고 셀에는 보이지 않는다는 점입니다.
예제 데이터에서 우편번호를 입력하기 전에 아포스트로피(')를 먼저 입력하고 Enter를 누르면(즉, '04330이 아니라 04330 대신 '04330을 입력), 앞자리 0이 포함된 우편번호가 표시됩니다.
4. TEXT 함수 적용하기
TEXT 함수는 워크시트에서 숫자를 텍스트로 변환하는 데 유용합니다. 아래 단계를 따라 해보세요.
방법
- 빈 셀(예: D5)을 선택합니다.
- 다음 수식을 입력합니다:
=TEXT(C5, "00000")
여기서 C5는 우편번호 값이고, "00000"은 원하는 서식으로, 우편번호가 5자리임을 의미합니다.
- Enter를 누릅니다.
5. RIGHT 함수 활용하기
RIGHT 함수는 지정한 문자 수만큼 텍스트 문자열의 오른쪽부터 문자를 반환합니다. 아래 단계를 따라 해보세요.
방법
- 빈 셀(예: D5)을 선택합니다.
- 다음 수식을 입력합니다:
=RIGHT("00000"&C5,5)
여기서 C5는 우편번호 값, "00000"은 원하는 서식, 5는 반환할 문자 수입니다.
- Enter를 누릅니다.
6. BASE 함수 활용하기
BASE 함수는 숫자를 지정된 진수(radix)의 텍스트 표현으로 변환합니다. 아래 단계를 따라 해보세요.
방법
- 빈 셀(예: D5)을 선택합니다.
- 다음 수식을 입력합니다:
=BASE(C5,10,5)
여기서 C5는 우편번호 숫자, 10은 진수, 5는 원하는 문자 길이입니다.
- Enter를 누릅니다.
7. CONCATENATE 함수 적용하기
CONCATENATE 함수는 두 개 이상의 텍스트 문자열을 하나로 연결합니다. 이를 활용해 숫자를 앞자리 0이 있는 텍스트로 변환할 수 있습니다.
방법
- 빈 셀(예: D5)을 선택합니다.
- 다음 수식을 입력합니다:
=CONCATENATE("00", C5)
여기서 "00"은 첫 번째 항목이고, C5는 두 번째 항목, 즉 우편번호 숫자입니다.
- Enter를 눌러 수식을 적용합니다.
- 그런 다음 채우기 핸들을 열 방향으로 드래그합니다.
참고: CONCATENATE 함수는 앞자리에 0을 붙일(add) 수는 있지만, 전체 자릿수에 맞게 0을 채워 넣는(pad) 기능은 없습니다.
8. REPT와 LEN 함수 조합하기
REPT 함수는 텍스트를 지정한 횟수만큼 반복하고, LEN 함수는 텍스트 문자열의 문자 수를 반환합니다. 두 함수를 조합하면 숫자를 앞자리 0이 있는 텍스트로 변환할 수 있습니다.
방법
- 빈 셀(예: E5)을 선택합니다.
- 다음 수식을 입력합니다:
=REPT(0,5)
여기서 0은 반복할 항목이고, 5는 반복 횟수입니다.
- Enter를 누릅니다.
결과가 모두 0으로만 표시됩니다. 따라서 C열과 D열의 값을 병합해야 합니다. 셀 E5 또는 새 워크시트의 빈 셀을 선택하고 다음 수식을 입력합니다.
=REPT(0,5)&C5
하지만 결과를 보면 여전히 자릿수가 맞춰져 있지 않습니다. 즉, 모든 결과가 앞자리 0이 포함된 5자리 텍스트가 아닙니다. 이때 LEN 함수를 추가로 사용합니다. 이전 셀 E5 또는 빈 셀에 다음 수식을 입력합니다.
=REPT(0,5-LEN(C5))&C5
여기서 C5는 우편번호 숫자입니다. Enter를 누르면 완벽하게 5자리로 맞춰진 결과를 얻을 수 있습니다.
9. 파워 쿼리 편집기 활용하기
Text.PadStart 함수는 지정한 텍스트의 시작 부분을 패딩하여 정해진 길이의 텍스트를 반환합니다. 이 함수를 사용하면 숫자를 앞자리 0이 있는 텍스트로 변환할 수 있습니다.
방법
- 셀 범위 B4:B13을 선택합니다.
- 데이터 탭으로 이동합니다.
- 표/범위에서 옵션을 선택합니다.
- 선택한 표를 확인하고 확인을 누릅니다.
이제 수식에 맞게 우편번호(숫자)를 텍스트로 변환해야 합니다.
- 커서를 왼쪽 상단 모서리로 이동합니다.
- 숫자를 텍스트로 변환하기 위해 텍스트 옵션을 선택합니다.
이제 함수를 사용하기 위해 새 열을 추가해야 합니다.
- 열 추가 탭을 클릭합니다.
- 사용자 지정 열을 선택합니다.
- 'Text with Leading Zeros (Padded)'처럼 새 열 이름을 입력합니다.
- 다음 수식을 입력합니다:
=Text.PadStart([#" ZIP Code (Number)"],5,"0")
여기서 'ZIP Code (Number)' 열은 텍스트로 입력되며, 5는 자릿수, 0은 채울 문자입니다.
- 확인을 누릅니다.
- 닫기 및 로드 명령을 클릭합니다.
- 닫기 후 로드 위치 옵션을 선택합니다.
- 로드 옵션을 테이블로 선택합니다.
- 기존 워크시트의 C4 셀을 선택합니다.
- 로드를 누릅니다.
최종 결과는 다음과 같습니다.
엑셀에서 숫자를 자동으로 텍스트로 변환하는 방법
엑셀에서 숫자를 자동으로 텍스트로 변환하려면 셀 서식을 활용하면 됩니다. 셀 범위를 미리 텍스트로 변환해 두면, 이후 입력하는 모든 숫자가 자동으로 텍스트로 처리됩니다.
방법
- 먼저 C5부터 C13까지의 셀 범위를 선택합니다.
- 리본 메뉴에서 홈 탭으로 이동합니다.
- 숫자 섹션에서 아래 화살표를 클릭해 텍스트를 선택합니다.
이제 해당 범위에 숫자를 입력하면 자동으로 텍스트로 처리됩니다. 다만 숫자를 입력하면 텍스트 형식으로 저장된 숫자라는 오류 표시가 나타날 수 있습니다.
- 오류를 제거하려면 C5~C13 셀 범위를 다시 선택합니다.
- 아래 화살표를 클릭한 후 오류 무시를 선택합니다.
그러면 오류 표시가 제거된 깔끔한 결과를 얻을 수 있습니다.
💬 알아두어야 할 사항
Text.PadStart 함수를 사용하기 전에 데이터가 반드시 텍스트 형식인지 확인하세요. 또한, 단순히 0을 붙이는 것(added)인지, 자릿수에 맞게 0을 채우는 것(padded)인지 요구 사항을 명확히 해야 합니다.
단순히 붙이는 앞자리 0은 숫자 앞에 접두어로 추가되는 것이고, 채워 넣는(pad) 방식은 숫자와 병합되어 지정된 자릿수를 완성하는 방식입니다.
결론
지금까지 엑셀에서 숫자를 앞자리 0이 포함된 텍스트로 변환하는 9가지 효과적인 방법을 살펴보았습니다. 위의 모든 방법이 유용하지만, 어떤 방법이 최적인지는 작업 상황과 요구 사항에 따라 달라집니다. 자신의 데이터에 가장 적합한 방법을 선택하세요. 더 궁금한 점이 있다면 댓글로 의견을 공유해 주세요.
함께 보면 좋은 글
- 날짜를 YYYYMMDD 형식의 텍스트로 변환하는 방법 (3가지 빠른 방법)
- 엑셀에서 날짜를 월 이름 텍스트로 변환하는 방법 (8가지 빠른 방법)
- 엑셀에서 텍스트를 숫자로 변환하는 방법
- 엑셀에서 앞자리 0 제거하기 (7가지 쉬운 방법 + VBA)