Excel에서 선행 0을 추가하는 방법

이 자습서에서는 Excel에서 선행 0을 추가하거나 유지하고 선행 0을 제거하는 다양한 방법을 다룹니다.

000652와 같이 하나 이상의 선행 0이 있는 숫자를 입력하거나 가져올 때마다 Excel에서 자동으로 해당 0을 제거하고 숫자 자체만 셀(652)에 표시됩니다. 이는 선행 0이 계산에 필요하지 않고 계산되지 않기 때문입니다.

그러나 ID 번호, 전화 번호, 신용 카드 번호, 제품 코드 또는 우편 번호 등을 입력할 때와 같이 선행 0이 필요한 경우가 있습니다. 다행히 Excel에서는 선행 0을 추가하거나 유지할 수 있는 여러 가지 방법을 제공합니다. 세포에서. 이 기사에서는 선행 0을 추가하거나 유지하고 선행 0을 제거하는 다양한 방법을 보여줍니다.

Excel에서 선행 0 추가

기본적으로 선행 0을 추가하는 데 사용할 수 있는 두 가지 방법이 있습니다. 두 번째, 사용자 지정 서식을 사용하여 선행 0을 추가합니다. 사용하려는 방법은 번호로 수행하려는 작업에 따라 다를 수 있습니다.

고유한 ID 번호, 계좌 번호, 주민등록번호 또는 우편번호 등을 입력할 때 앞에 0을 추가할 수 있습니다. 그러나 이 숫자를 계산이나 함수에 사용하지 않을 것이므로 변환하는 것이 가장 좋습니다. 숫자를 텍스트로. 전화 번호 또는 계좌 번호를 합산하거나 평균화하지 않을 것입니다.

숫자를 텍스트로 서식을 지정하여 숫자 앞에 0을 추가하거나 채울 수 있는 몇 가지 방법이 있습니다.

  • 셀 형식을 텍스트로 변경
  • 아포스트로피(') 추가
  • TEXT 기능 사용
  • REPT/LEN 기능 사용하기
  • CONCATENATE 함수/앰퍼샌드 연산자(&) 사용
  • RIGHT 기능 사용하기

셀 형식을 텍스트로 변경

이것은 숫자에 선행 0을 추가하는 가장 간단한 방법 중 하나입니다. 숫자만 입력하려고 하고 입력할 때 선행 0을 유지하려는 경우 이 방법이 적합합니다. 셀 형식을 일반 또는 숫자에서 텍스트로 변경하면 Excel에서 숫자를 텍스트 값으로 처리하도록 강제할 수 있으며 셀에 입력하는 모든 내용은 정확히 동일하게 유지됩니다. 방법은 다음과 같습니다.

선행 0을 추가할 셀을 선택합니다. '홈' 탭으로 이동하여 숫자 그룹에서 '형식' 드롭다운 상자를 클릭하고 형식 옵션에서 '텍스트'를 선택합니다.

이제 숫자를 입력할 때 Excel은 숫자에서 선행 0을 삭제하지 않습니다.

셀의 왼쪽 상단 모서리에 작은 녹색 삼각형(오류 표시기)이 표시될 수 있으며 해당 셀을 선택하면 숫자를 텍스트로 저장했음을 나타내는 경고 기호가 표시됩니다.

오류 메시지를 제거하려면 셀을 선택하고 경고 기호를 클릭한 다음 목록에서 '오류 무시'를 선택합니다.

숫자 사이에 공백이나 하이픈을 사용하여 전화번호를 입력할 수도 있습니다. Excel은 이 숫자를 자동으로 텍스트로 처리합니다.

행간 사용 아포스트로피( ' )

Excel에서 선행 0을 추가하는 또 다른 방법은 숫자 시작 부분에 아포스트로피(')를 추가하는 것입니다. 이렇게 하면 Excel에서 숫자를 텍스트로 입력하게 됩니다.

숫자 앞에 아포스트로피를 입력하고 'Enter'를 누르십시오. Excel은 선행 0을 그대로 유지하지만 셀을 선택하지 않는 한 워크시트에 (')가 표시되지 않습니다.

텍스트 기능 사용

위의 방법은 숫자를 입력할 때 숫자에 0을 추가하지만 이미 숫자 목록이 있고 그 앞에 선행 0을 채우려는 경우 TEXT 함수가 올바른 방법입니다. TEXT 기능을 사용하면 사용자 지정 서식을 적용하는 동안 숫자를 텍스트 문자열로 변환할 수 있습니다.

TEXT 함수의 구문:

= TEXT( 값, 형식_텍스트)

어디에,

  • 값 - 텍스트로 변환하고 서식을 적용해야 하는 숫자 값입니다.
  • 형식_텍스트 – 적용하려는 형식입니다.

TEXT 기능을 사용하여 숫자가 몇 자릿수여야 하는지 지정할 수 있습니다. 예를 들어 숫자를 8자리로 지정하려면 함수의 두 번째 인수인 "00000000"에 0을 8개 입력합니다. 셀에 6자리 숫자가 있는 경우 함수는 수동으로 2개의 선행 0을 추가하고 56과 같은 2자리 숫자가 있는 경우 나머지는 0(00000056)이 됩니다.

예를 들어, 선행 0을 추가하고 숫자를 6자리 길이로 만들려면 다음 공식을 사용하십시오.

=TEXT(A2,"000000")

수식의 두 번째 인수에 6개의 0이 있으므로 함수는 숫자 문자열을 텍스트 문자열로 변환하고 5개의 선행 0을 추가하여 문자열을 6자리 길이로 만듭니다.

메모: 함수에서 형식 코드를 큰따옴표로 묶는 것을 기억하십시오.

이제 채우기 핸들을 끌어 나머지 셀에 동일한 수식을 적용할 수 있습니다. 보시다시피, 함수는 숫자를 텍스트로 변환하고 숫자 앞에 0을 추가하여 총 자릿수가 6이 되도록 합니다.

TEXT 함수는 항상 숫자가 아닌 텍스트 문자열로 값을 반환하므로 산술 계산에서는 사용할 수 없지만 VLOOKUP 또는 INDEX/MATCH와 같은 조회 수식에서 계속 사용할 수 있습니다. 제품 ID를 사용하는 제품.

CONCATENATE 함수/앰퍼샌드 연산자(&) 사용

열의 모든 숫자 앞에 고정된 수의 선행 0을 추가하려면 CONCATENATE 함수 또는 앰퍼샌드 연산자(&)를 사용할 수 있습니다.

CONCATENATE 함수의 구문:

=CONCATENATE(텍스트1, [텍스트2], ...)

어디에,

텍스트 1 - 숫자 앞에 삽입할 0의 수입니다.

텍스트2 – 원래 번호 또는 셀 참조

앰퍼샌드 연산자의 구문:

=값_1 및 값_2

어디에,

Value_1은 숫자 앞에 삽입할 선행 0이고 Value_2는 숫자입니다.

예를 들어 숫자 앞에 두 개의 0만 추가하려면 다음 공식 중 하나를 사용합니다.

=연결("00",A2)

첫 번째 인수는 A2(두 번째 인수)의 숫자 앞에 두 개의 0을 채우고 싶기 때문에 두 개의 0("00")입니다.

또는, 

="00"&A2

여기서 첫 번째 인수는 2개의 0이고 뒤에 '&' 연산자가 있고 두 번째 인수는 숫자입니다.

보시다시피 수식은 숫자에 포함된 자릿수에 관계없이 열의 모든 숫자에 두 개의 선행 0을 추가합니다.

이 두 수식은 모두 원래 숫자 앞에 특정 수의 0을 결합하여 텍스트 문자열로 저장합니다.

REPT/LEN 기능 사용하기

숫자 또는 영숫자 데이터에 선행 0을 추가하고 문자열을 텍스트로 변환하려면 REPT 함수를 사용하십시오. REPT 함수는 특정 횟수만큼 문자를 반복하는 데 사용됩니다. 이 함수는 숫자 앞에 고정된 숫자의 선행 0을 삽입하는 데에도 사용할 수 있습니다.

=REPT(텍스트, number_times)

여기서 'text'는 반복하려는 문자(이 경우 '0')이고 'number_times' 인수는 해당 문자를 반복하려는 횟수입니다.

예를 들어, 숫자 앞에 5개의 0을 생성하려면 공식은 다음과 같습니다.

=REPT(0,5)&A2

공식이 하는 일은 5개의 0을 반복하고 A2의 숫자 문자열을 결합하고 결과를 반환하는 것입니다. 그런 다음 채우기 핸들을 사용하여 B2:B6 셀에 수식이 적용됩니다.

위의 공식은 숫자 앞에 고정된 수의 0을 추가하지만 숫자의 총 길이는 숫자에 따라 다릅니다.

특정 문자 길이(고정 길이) 문자열을 생성하는 데 필요한 위치에 선행 0을 추가하려는 경우 REPT 및 LEN 함수를 함께 사용할 수 있습니다.

통사론:

=REPT(텍스트, number_times-LEN(텍스트))&셀

예를 들어, A2의 값에 접두사 0을 추가하고 5자의 긴 문자열을 만들려면 다음 공식을 시도하십시오.

=REPT(0,5-LEN(A2))&A2

여기서 'LEN(A2)'는 A2 셀에 있는 문자열/숫자의 총 길이를 가져옵니다. '5'는 셀이 가져야 하는 문자열/숫자의 최대 길이입니다. 그리고 'REPT(0,5-LEN(A2))' 부분은 최대 0 수(5)에서 A2의 문자열 길이를 빼서 0의 수를 더합니다. 그런 다음 고정 길이 문자열을 만들기 위해 A2 값 앞에 0의 숫자가 결합됩니다.

RIGHT 기능 사용하기

Excel에서 문자열 앞에 선행 0을 채우는 또 다른 방법은 RIGHT 함수를 사용하는 것입니다.

RIGHT 함수는 숫자 시작 부분에 0을 추가하고 값에서 가장 오른쪽에 있는 N개의 문자를 추출할 수 있습니다.

통사론:

= 오른쪽(텍스트, num_chars)
  • 텍스트 문자를 추출할 셀 또는 값입니다.
  • num_chars 텍스트에서 추출할 문자 수입니다. 이 인수가 제공되지 않으면 첫 번째 문자만 추출됩니다.

이 방법의 경우 'text' 인수에 문자열이 포함된 셀 참조와 최대 0 수를 연결합니다.

선행 0이 있는 A의 숫자 문자열을 기반으로 6자리 숫자를 생성하려면 다음 공식을 시도하십시오.

=오른쪽("0000000"&A2,6)

수식의 첫 번째 인수(텍스트)는 A2("0000000"&A2)의 값에 7개의 0을 추가한 다음 맨 오른쪽에 있는 7개의 문자를 반환하므로 일부 선행 0이 표시됩니다.

사용자 지정 숫자 서식을 사용하여 선행 0 추가

위의 방법 중 하나를 사용하여 숫자 앞에 선행 0을 넣으면 항상 숫자가 아닌 텍스트 문자열이 표시됩니다. 그리고 그들은 계산이나 숫자 공식에서 많이 사용되지 않을 것입니다.

Excel에서 선행 0을 추가하는 가장 좋은 방법은 사용자 지정 숫자 서식을 적용하는 것입니다. 셀에 사용자 정의 숫자 포맷을 추가하여 선행 0을 추가하면 셀 값이 변경되지 않고 표시되는 방식만 변경됩니다. 값은 여전히 ​​텍스트가 아닌 숫자로 유지됩니다.

셀의 숫자 서식을 변경하려면 다음 단계를 따르세요.

선행 0을 표시할 셀 또는 셀 범위를 선택합니다. 그런 다음 선택한 범위 내의 아무 곳이나 마우스 오른쪽 버튼으로 클릭하고 상황에 맞는 메뉴에서 '셀 서식' 옵션을 선택합니다. 또는 단축키 Ctrl + 1을 누르십시오.

셀 서식 창에서 '숫자' 탭으로 이동하여 범주 옵션에서 '사용자 지정'을 선택합니다.

'유형:' 상자에 0의 수를 입력하여 셀에 표시하려는 총 자릿수를 지정합니다. 예를 들어 숫자를 6자리로 지정하려면 사용자 지정 형식 코드로 '000000'을 입력합니다. 그런 다음 '확인'을 클릭하여 적용하십시오.

이렇게 하면 숫자 앞에 선행 0이 표시되고 숫자가 6자리 미만이면 앞에 0을 채웁니다.

숫자에는 선행 0만 있는 것처럼 보이지만 기본 값은 변경되지 않은 상태로 유지됩니다. 사용자 지정 서식이 있는 셀을 선택하면 수식 입력줄에 원래 숫자가 표시됩니다.

사용자 지정 숫자 형식으로 사용할 수 있는 디지털 자리 표시자가 많이 있습니다. 그러나 숫자에 선행 0을 추가하는 데 사용할 수 있는 기본 자리 표시자는 두 개뿐입니다.

  • 0 – 추가 0을 표시하는 숫자 자리 표시자입니다. 숫자가 값과 관련이 있는지 여부에 관계없이 강제 숫자 0-9를 표시합니다. 예를 들어 형식 코드 000.00으로 2.5를 입력하면 002.50이 표시됩니다.
  • # – 선택적 숫자를 표시하고 추가 0을 포함하지 않는 것은 숫자 자리 표시자입니다. 예를 들어 형식 코드 000#과 함께 123을 입력하면 0123이 표시됩니다.

또한 형식 코드에 포함된 구두점이나 기타 문자가 있는 그대로 표시됩니다. 하이픈(-), 쉼표(,), 슬래시(/) 등과 같은 문자를 사용할 수 있습니다.

예를 들어 사용자 지정 형식을 사용하여 전화번호로 숫자 형식을 지정할 수도 있습니다.

셀 서식 대화 상자의 서식 코드:

결과:

다음 예제에서 이 서식 코드를 적용해 보겠습니다.

##0000

보시다시피 '0'은 추가 0을 추가하는 반면 '#'은 중요하지 않은 0을 추가하지 않습니다.

우편 번호, 전화 번호 및 주민등록번호에 대한 셀 서식 대화 상자의 '특수 서식' 섹션에서 미리 정의된 서식 코드를 사용할 수도 있습니다.

다음 표는 다른 '특수' 형식 코드가 다른 열에 적용되는 선행 0이 있는 숫자를 보여줍니다.

Excel에서 선행 0 제거

이제 Excel에서 선행 0을 추가하는 방법을 배웠습니다. 문자열 수에서 선행 0을 제거하는 방법을 살펴보겠습니다. 경우에 따라 외부 소스에서 데이터를 가져올 때 숫자에 접두사가 0이 되고 텍스트 형식이 지정될 수 있습니다. 이러한 경우 수식에서 사용할 수 있도록 선행 0을 제거하고 숫자로 다시 변환해야 합니다.

Excel에서 선행 0을 제거할 수 있는 다양한 방법이 있으며 하나씩 살펴보겠습니다.

셀 서식을 변경하여 선행 0 제거

사용자 지정 숫자 서식으로 선행 0을 추가한 경우 셀 형식을 변경하여 쉽게 제거할 수 있습니다. 주소 표시줄을 보면 셀이 사용자 지정 형식인지 알 수 있습니다(0은 주소 표시줄이 아닌 셀에 표시됨).

접두사 0을 제거하려면 선행 0이 있는 셀을 선택하고 '숫자 형식' 상자를 클릭한 다음 '일반' 또는 '숫자' 서식 옵션을 선택합니다.

이제 선행 0이 사라졌습니다.

텍스트를 숫자로 변환하여 선행 0 삭제

셀 형식을 변경하거나 숫자 앞에 아포스트로피를 추가하여 선행 0을 추가했거나 데이터를 가져올 때 자동으로 추가된 경우, 이를 숫자로 변환하는 가장 쉬운 방법은 오류 검사 옵션을 사용하는 것입니다. 방법은 다음과 같습니다.

숫자가 왼쪽 정렬되고 셀의 왼쪽 상단 모서리에 작은 녹색 삼각형(오류 표시기)이 있는 경우 이 방법을 사용할 수 있습니다. 이것은 숫자가 텍스트로 서식이 지정되었음을 의미합니다.

해당 셀을 선택하면 선택 항목의 오른쪽 상단에 노란색 경고가 표시됩니다. 그런 다음 드롭다운에서 '숫자로 변환' 옵션을 클릭합니다.

0이 제거되고 숫자가 숫자 형식(오른쪽 정렬)으로 다시 변환됩니다.

선행 0 제거 1로 곱하기/나누기

선행을 제거하는 또 다른 쉽고 좋은 방법은 숫자를 1로 곱하거나 나누는 것입니다. 값을 나누거나 곱해도 값이 변경되지 않고 단순히 값을 다시 숫자로 변환하고 선행 0을 제거합니다.

이렇게 하려면 셀에 아래 예제의 수식을 입력하고 Enter 키를 누릅니다. 선행 0이 제거되고 문자열이 숫자로 다시 변환됩니다.

그런 다음 채우기 핸들을 사용하여 이 수식을 다른 셀에 적용합니다.

'선택하여 붙여넣기' 명령을 사용하여 동일한 결과를 얻을 수 있습니다. 방법은 다음과 같습니다.

셀에 '1' 숫자 값을 입력하고(예: B2) 해당 값을 복사합니다.

그런 다음 선행 0을 제거할 셀을 선택합니다. 그런 다음 선택 항목을 마우스 오른쪽 버튼으로 클릭한 다음 '선택하여 붙여넣기' 옵션을 선택합니다.

선택하여 붙여넣기 대화 상자의 작업에서 '곱하기' 또는 '나누기' 옵션을 선택하고 '확인'을 클릭합니다.

즉, 선행 0이 제거되고 문자열은 숫자로 남습니다.

수식을 사용하여 선행 0 제거

접두사 0을 삭제하는 또 다른 쉬운 방법은 VALUE 함수를 사용하는 것입니다. 이 방법은 다른 수식이나 아포스트로피를 사용하거나 사용자 지정 서식을 사용하여 선행 0을 추가했는지 여부에 관계없이 유용할 수 있습니다.

=값(A1)

수식의 인수는 값 또는 값이 있는 셀 참조일 수 있습니다. 수식은 선행 0을 제거하고 값을 텍스트에서 숫자로 변환합니다. 그런 다음 나머지 셀에 수식을 적용합니다.

경우에 따라 선행 0을 제거하고 숫자를 텍스트 형식으로 유지하고 싶을 수 있습니다. 이러한 경우 다음과 같이 TEXT() 및 VALUE() 함수를 함께 사용해야 합니다.

=TEXT(VALUE(A1),#")

VALUE 함수는 A1의 값을 숫자로 변환합니다. 그러나 두 번째 인수인 '#'은 값을 추가 0 없이 텍스트 형식으로 다시 변환합니다. 결과적으로 선행 0 없이 숫자를 얻을 수 있지만 여전히 텍스트 형식(왼쪽 정렬)입니다.

Excel의 Text to Columns 기능을 사용하여 선행 0 제거

선행 0을 제거하는 또 다른 방법은 Excel의 Text to Columns 기능을 사용하는 것입니다.

선행 0이 있는 숫자가 있는 셀 범위를 선택합니다.

그런 다음 '데이터' 탭으로 이동하여 데이터 도구 그룹에서 '텍스트를 열로' 버튼을 클릭합니다.

'텍스트를 열로 변환' 마법사가 나타납니다. 3단계 중 1단계에서 '구분'을 선택하고 '다음'을 클릭합니다.

2/3단계에서 모든 구분 기호를 선택 취소하고 '다음'을 클릭합니다.

마지막 단계에서 열 데이터 형식 옵션을 '일반'으로 두고 앞에 0이 없는 숫자를 원하는 대상(범위의 첫 번째 셀)을 선택합니다. 그런 다음 '마침'을 클릭하십시오.

그리고 아래와 같이 별도의 열에서 선행이 제거된 숫자를 얻을 수 있습니다.

그게 다야.