사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 텍스트 형식 숫자를 숫자처럼 정렬하기

 

 

 

 

엑셀에서는 1-1, 1-2와 같은 값은 숫자로 인식하지 않아서 이런 값을 정렬하게 되면 1-10은 1-2보다 앞에 표시된다. 1-10의 뒷 번호인 10을 숫자 값으로 인식하지 않고, 10의 앞 문서인 1을 우선 인식하여 2보다 작은 값이라고 판단하기 때문이다. 이럴 경우 어떤식으로 정렬을 진행해야 하는지 방법을 알아보자.

 

거래처관리.xlsx

0.01MB

 

위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다.

 

 

[B5:B15] 범위의 텍스트 형식의 숫자처럼 정렬해 보자. 먼저 정렬 기능을 이용해서 정렬할 경우 어떻게 되는지 확인하기 위해 [B5]셀을 선택하고 [데이터] - [정렬 및 필터] 그룹 - [오름차순 정렬]을 클릭한다. [정렬기준]에 [제품번호]를 선택하고 [확인] 단추를 누른다.

 

 

[정렬 경고]라는 대화상자가 나타나는데 여기에서 선택하는 방법에 따라 정렬방식이 달라진다. 먼저 '일반 숫자와 텍스트로 저장된 숫자를 모두 숫자로 정렬(N)'을 선택하고 [확인] 단추를 누른다.

 

 

이 경우에는 제품번호가 상기 화면과 같이 1-12가 1-3의 아래에 정렬이 된 것을 확인할 수 있다. [Ctrl + Z]를 눌러서 이전상태로 되돌리기를 한다. 다시 [B5]셀을 선택하고 [데이터] - [정렬 및 필터] 그룹 - [오름차순 정렬]을 클릭한다. [정렬기준]에 [제품번호]를 선택하고 [확인] 단추를 누른다.

 

 

[정렬 경고]라는 대화상자가 나타나면 이번에는 '일반 숫자와 텍스트로 저장된 숫자를 구분하여 정렬(T)'을 선택하고 [확인] 단추를 누른다.

 

 

이번에는 처음에 설명했던 것과 같이 1-12와 1-14가 1-3의 윗쪽에 위치하여 정렬된 것을 확인할 수 있다. 이번에는 번거기는 하지만 다른 키를 설정해서 정렬을 하는 방법을 알아보자.

 

 

먼저 [I4] 셀을 클릭하여 '정렬기준'이라고 입력하고 [I4]셀에는 아래와 같이 수식을 입력하여 채우기 핸들을 이용하여 [I15]까지 드래그하여 채워보자.

 

=LEFT(B5,FIND("-",B5)-1)&TEXT(MID(B5,FIND("-",B5)+1,100),"00")

 

이 수식은 [B5] 셀에 입력된 값을 '-' 문자 전(LEFT(B5,FIND("-",B5)-1))과 후(MID(B5,FIND("-",B5)+1,100))으로 나뉘어 진다. 그리고 다시 연결 연산자(&)를 이용해 연결할 때 TEXT 함수를 사용하여 "-" 문자 뒤의 숫자를 2자리 숫자로 변환한다. 이렇게 하면 1-2는 102가 되고, 1-12는 112가 되어 숫자처럼 정렬되게 된다.

 

 

[B5]셀을 선택하고 [데이터] - [정렬 및 필터] 그룹 - [오름차순 정렬]을 클릭한다. [정렬기준]에 [정렬기준]를 선택하고 [확인] 단추를 누른다.

 

 

마찬가지로 [정렬 경고]라는 대화상자가 나타나지만 어떤 항목을 선택하던 결과 값은 똑같이 나타나게 된다.