엑셀 실무] 표시 형식과 TEXT 함수중 어떤 것을 사용해야 할까?

 

실습 파일 다운로드

데이터 입력과 표시형식_실습.xlsx

0.01MB

 

안녕하세요. 짤막한 강좌 이충욱 강사입니다. 

 

주민등록번호를 직접 입력해야 하는 경우 앞자리와 뒷자리를 구분하는 빼기(-) 기호를 입력하는 것과 입력하지 않는 것에 따라 엑셀이 인식하는 데이터 형식이 달라지게 됩니다.

 

빼기 기호를 입력하지 않는 경우엔 숫자 데이터 형식으로 인식하고, 빼기 기호를 입력하는 경우엔 문자 데이터 형식으로 인식합니다.

 

빼기 기호를 입력하지 않고 숫자만 입력하면 한 글자 더 적게 입력해도 되기 때문에 상대적으로 나은 방법일 수 있으나 숫자 데이터 형식으로 인식해서 앞부분에 입력된 숫자 0을 유효하지 않는 값으로 인식하기 때문에 없애 버립니다.

 

이런 문제를 해결하기 위해 표시 형식 기능을 이용합니다.

 

가상으로 2000년 1월 1일 출생한 사람의 주민등록번호를 예를 들어 나타내 보겠습니다.

뒷자리는 임의로 3123456으로 입력했습니다.

 

입력을 완료하면 1013123456으로 나타납니다.

 

주민등록번호 형식으로 나타나도록 하기 위해 단축키 <Ctrl + 1>을 눌러 [셀 서식] 대화상자를 표시하고 [표시 형식] 탭에서 [기타] 범주에서 [주민등록번호] 형식을 선택합니다.

주민등록번호 형식에 맞게 화면에 표시됩니다.

표시 형식 기능을 이용해서 입력된 값을 주민등록번호 형식으로 나타냈을 때 화면에 표시되는 모양은 주민등록번호 형식이 맞게 보이지만, 실제 입력된 값은 주민등록번호 형식과 다르기 때문에 문제가 발생할 수 있습니다.

 

대표적인 것이 주민등록번호에 표시된 값을 이용해서 생년월일을 구하거나 성별을 나타내고 하는 경우입니다.

 

생년월일을 구하기 위해 주민등록번호에서 앞 두 자리를 잘라내어 연도, 세번째부터 두 자리를 잘라내어 월, 다섯 번째부터 두 자리를 일을 구하려 하는 경우 실제 입력된 값이 이 형식에 맞지 않게 됩니다.

 

표시 형식 기능을 이용해 나타내는 것은 겉으로 보이는 모습만 바꾸는 것이기 때문입니다.

 

그래서 주민등록번호를 입력할 때 표시 형식 기능을 이용하지 말고 전체 주민등록번호를 모두 입력하는 것이 앞으로 발생할 수 있는 문제를 막는 방법입니다.

 

표시 형식 기능을 이용해 사용자가 원하는 모습으로 나타내는 것은 좋은 기능이긴 하지만 어떤 값들은 직접 입력해야 하는 겁니다.

 

새로운 데이터를 입력하는 경우에는 직접 모두 입력하면 되겠지만 이미 입력되어 있는 데이터는 어떻게 할까요?

 

실습 파일은 이미 주민번호가 입력되어 있습니다.

이런 경우 TEXT 함수와 선택하여 붙여넣기-값 기능으로 직접 입력한 데이터로 바꿀 수 있습니다.

 

TEXT 함수는 표시 형식 기능을 수식에서 사용할 수 있도록 만든 함수입니다.

 

[E3] 셀에 커서를 두고 =te를 입력하면 함수 목록에 TEXT 함수가 나타납니다.

더블 클릭한 뒤 단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

 

Value 인수에 [D3] 셀을 선택해 나타냅니다.

Format_text 인수에 "000000-0000000" 형식을 입력합니다.

Format_text 인수를 입력할 때 큰 따옴표도 직접 적어야 합니다.

 

주민등록번호 형식에 맞게 표시됩니다.

입력된 데이터 마지막까지 채우기 합니다.

범위 선택이 되어 있는 이 상태에서 바로 마우스 오른쪽 단추를 눌러 복사 합니다.

 

[D3] 셀을 선택해 마우스 오른쪽 단추를 눌러 [붙여넣기 옵션] 중 [선택하여 붙여넣기-값]에 해당하는 아이콘을 누릅니다.

마지막으로 E열 문자를 선택한 다음 [삭제]를 선택해 E열 내용을 지웁니다.

주민등록번호 형식에 맞지 않던 데이터가 모두 형식에 맞게 수정되었습니다.

필요하신 분께 도움이 되길 바랍니다.