이 글에서는 엑셀에서 0을 제거하는 다양한 방법을 소개합니다. 직접 만들지 않은 스프레드시트를 작업하다 보면 셀마다 서로 다른 숫자 형식이 적용되어 있는 경우를 자주 접하게 됩니다. 예를 들어 전화번호에는 앞자리에 0이 붙어 있거나, 일부 셀에는 값 자체가 0으로만 되어 있어 평균 계산 같은 후속 연산에 영향을 줄 수 있습니다. 다행히 엑셀에서는 이 두 가지 유형의 0을 모두 손쉽게 제거할 수 있는 여러 기능을 제공합니다. 지금부터 하나씩 살펴보겠습니다.
연습용 워크북 다운로드
이 글을 준비하면서 사용한 연습용 워크북을 다운로드하여 함께 따라 해보실 수 있습니다.
엑셀에서 0을 제거하는 7가지 쉬운 방법
1. 찾기 및 바꾸기 옵션으로 0 삭제하기
데이터 범위에서 값이 0인 셀을 한 번에 삭제하고 싶다면 찾기 및 바꾸기 기능이 가장 간편합니다. 단계별로 살펴보겠습니다.
실행 단계:
- 먼저 전체 데이터 범위(B5:B13)를 선택합니다.
- Ctrl+H 키를 눌러 찾기 및 바꾸기 창을 엽니다. 바꾸기 탭에서 찾을 내용 필드에 0을 입력하고, 바꿀 내용 필드는 비워 둡니다. 그런 다음 '셀 내용 전체 일치' 옵션에 체크합니다. 이 옵션을 선택해야 값이 0인 셀만 대상이 되며, 100, 80, 90처럼 숫자 중간에 포함된 0은 그대로 유지됩니다. 설정이 끝나면 모두 바꾸기 버튼을 클릭합니다.
- 엑셀이 몇 개의 0 값 셀이 빈 칸으로 바뀌었는지 알려줍니다. 확인 버튼을 클릭합니다.
- 결과 화면입니다. 데이터 세트에서 모든 0이 깔끔하게 제거되었습니다.
2. 오류 검사 옵션으로 앞자리 0 제거하기 (텍스트를 숫자로 변환)
종종 앞자리 0을 표시하기 위해 셀 서식을 텍스트로 지정하는 경우가 있습니다. 이렇게 저장된 앞자리 0을 한 번에 지우려면 클릭 한 번으로 텍스트를 숫자로 변환하면 됩니다.
실행 단계:
- 앞자리 0이 포함된 데이터 범위(B5:B13)를 선택합니다. 그러면 선택 영역의 왼쪽 위 모서리에 노란색 아이콘이 나타납니다.
- 노란색 오류 검사 아이콘을 클릭한 뒤 드롭다운 메뉴에서 '숫자로 변환' 옵션을 선택합니다.
- 마지막으로 확인해 보면 모든 앞자리 0이 사라진 것을 볼 수 있습니다.
3. 사용자 지정 숫자 서식 변경으로 앞자리 0 지우기
데이터 세트에 사용자 지정 숫자 서식이 적용된 경우도 있습니다. 예를 들어 값과 상관없이 항상 일정한 자릿수로 표시되도록 설정된 셀들이 그렇습니다. 이럴 때는 서식을 일반 형식으로 바꾸는 것만으로도 앞자리 0을 제거할 수 있습니다.
실행 단계:
- 먼저 전체 데이터 범위(B5:B11)를 선택합니다.
- 홈 탭의 숫자 그룹으로 이동합니다. 현재 특수 숫자 서식이 적용되어 있을 것입니다.
- 드롭다운 목록에서 일반을 선택합니다.
- 아래와 같이 결과가 출력됩니다.
4. 선택하여 붙여넣기 기법으로 앞자리 0 삭제하기
선택하여 붙여넣기 기능을 활용하면 앞자리 0을 제거할 수 있습니다. 엑셀 셀의 기본 숫자 서식은 일반이며, 이 원리를 활용하는 방법입니다. 이 방법은 사용자 지정 숫자 서식과 텍스트로 변환된 숫자 모두에 적용됩니다. 값이 텍스트 형식과 사용자 지정 형식으로 섞여 있는 데이터 세트가 있다고 가정해 보겠습니다.
실행 단계:
- 먼저 빈 셀 하나를 선택한 뒤 복사합니다.
- 그다음 데이터 범위(B5:B13)를 선택하고 마우스 오른쪽 버튼을 클릭하여 선택하여 붙여넣기를 선택합니다.
- 선택하여 붙여넣기 창이 열리면 연산 항목에서 더하기를 선택하고 확인을 클릭합니다. 빈 값을 더하면 텍스트 형식의 숫자가 실제 숫자로 변환되면서 앞자리 0이 사라집니다.
- 아래와 같이 결과가 출력됩니다.
5. VALUE 함수로 앞자리 0 제거하기
지금까지의 방법과 달리, 이번에는 VALUE 함수 같은 엑셀 함수를 사용해 앞자리 0을 제거하는 방법을 알아보겠습니다. VALUE 함수는 숫자를 나타내는 텍스트 문자열을 실제 숫자 값으로 변환해 줍니다. 4번 방법과 마찬가지로 사용자 지정 숫자 서식과 텍스트로 변환된 숫자 모두에 적용할 수 있습니다.
실행 단계:
- C5 셀에 아래 수식을 입력합니다.
=VALUE(B5)
- 아래와 같은 결과가 출력됩니다. 채우기 핸들(+) 도구를 이용해 나머지 셀에도 수식을 복사합니다.
6. 함수 조합으로 텍스트에서 앞자리 0 제거하기
지금까지는 셀에 숫자만 들어 있는 경우의 0 제거 방법을 살펴봤습니다. 하지만 셀에 텍스트와 숫자가 함께 들어 있는 경우도 있습니다. 이런 상황에서는 여러 엑셀 함수를 조합하면 앞자리 0을 제거할 수 있습니다. 예를 들어 이번 방법에서는 RIGHT, LEN, FIND, LEFT, SUBSTITUTE 함수를 결합해 앞자리 0을 삭제합니다.
실행 단계:
- 먼저 C5 셀에 아래 수식을 입력합니다.
=RIGHT(B5,LEN(B5)-FIND(LEFT(SUBSTITUTE(B5,"0",""),1),B5)+1)
- 위 수식의 결과물은 아래와 같습니다.
수식 분석:
➤ SUBSTITUTE(B5,"0","")
SUBSTITUTE 함수는 문자열에서 0을 빈 칸("")으로 바꿉니다. 결과값은 'ABCD'입니다.
➤ LEFT(SUBSTITUTE(B5,"0",""),1)
LEFT 함수는 문자열의 맨 왼쪽 문자를 추출합니다. 결과값은 'A'입니다.
➤ FIND(LEFT(SUBSTITUTE(B5,"0",""),1),B5)
FIND 함수는 LEFT 수식이 반환한 맨 왼쪽 문자가 원본 문자열에서 위치한 곳을 찾아냅니다. 이 부분의 결과값은 '3'입니다.
그다음 FIND 수식의 결과에 1을 더해 전체 텍스트 문자열의 길이를 구합니다. 그리고 LEN 함수가 계산한 문자열 전체 길이에서 FIND 수식의 결과를 빼면 앞자리 0을 제외한 문자 수가 됩니다.
➤ RIGHT(B5,LEN(B5)-FIND(LEFT(SUBSTITUTE(B5,"0",""),1),B5)+1)
마지막으로 RIGHT 함수가 앞자리 0을 제외한 전체 텍스트 문자열을 추출합니다.
7. VBA로 엑셀에서 앞자리 0 삭제하기
VBA 매크로를 사용해서도 앞자리 0을 제거할 수 있습니다. 이 방법의 실행 단계를 살펴보겠습니다.
실행 단계:
- 먼저 전체 데이터 범위(B5:B13)를 선택합니다.
- 그다음 해당 워크시트 탭 이름 위에서 마우스 오른콧 버튼을 클릭하고 코드 보기를 선택합니다.
- 코드 모듈 창이 열리면 아래 코드를 입력합니다.
Sub RemoveZero()
Dim Range As Range
Dim WorkRange As Range
On Error Resume Next
xTitleId = "Excel"
Set WorkRange = Application.Selection
Set WorkRange = Application.InputBox("Range", xTitleId, WorkRange.Address, Type:=8)
WorkRange.NumberFormat = "General"
WorkRange.Value = WorkRange.Value
End Sub
- 입력이 끝나면 코드를 실행(Run)합니다.
- 마지막으로 확인하면 데이터 세트(B5:B11)에서 모든 앞자리 0이 사라진 것을 볼 수 있습니다.
마무리
지금까지 엑셀에서 0을 제거하는 7가지 방법을 자세히 살펴보았습니다. 찾기 및 바꾸기를 이용한 간단한 방법부터 함수 조합, VBA를 활용한 고급 방법까지 상황에 맞게 선택하시면 됩니다. 이 방법들을 활용하면 앞자리 0이나 값이 0인 셀 때문에 생기는 불편함을 손쉽게 해결할 수 있습니다. 궁금한 점이 있다면 언제든 문의해 주세요.