엑셀 문자와 숫자가 같이 있는 셀 정렬하는 방법(매크로)

 엑셀에서 문자와 숫자가 같이 있는 셀은 무조건 문자 취급을 하기 때문에 숫자 부분의 정렬은 한자리만 비교하게 되어 100과 9가 있다면 100이 먼저 정렬이 되게 된다.

 때문에 문자와 숫자가 같이 있는 셀을 정렬하기 위해서는 문자와 숫자를 분리한 셀을 별도로 만들어서 정렬을 하는 것이 좋은데 이것이 싫다면 매크로를 작성하는 것이 좋지만 이런 매크로는 작성이 쉬운 편은 아니기 때문에 숫자와 문자를 분리하여 정렬하는 방법을 우선으로 적용해 보고 분리한 값을 추가할 셀이 없는 경우 매크로 작성을 생각해 보는 것이 좋다.

목차

 

 

엑셀 문자와 숫자가 같이 있는 셀 정렬하기

1. 문자와 숫자 분리하기.

 문자와 숫자를 분리하기 위한 방법은 많이 있다.

 일단 문자와 숫자의 패턴이 정해져 있다면 left나 mid 함수를 이용하거나 리본 메뉴의 [데이터 -> 텍스트 나누기]를 사용하여 분리를 하면 되는데 원본 값은 유지하고 텍스트만 가져온다면 함수를 이용하는 것이 좋다.

 

 예를 들어 값의 패턴이 A100, A99와 같이 문자 하나 숫자 조합이라면 아래와 같은 방법으로 값을 가져올 수 있다.

- 문자 추출 =LEFT(B2,1) B2 셀의 값중 왼쪽에서 하나의 값을 가져온다. - 숫자 추출 =MID(B2,2,10) B2 셀의 값중 2번째 부터 10개의 값을 가져온다. 원래 값의 길이 보다 높게 설정해도 있는 값만 가져오니 넉넉하게 설정하면 된다.

2. 분리한 값을 기준으로 정렬 하기.

 값을 분리했다면 원본 값과 분리한 값을 모두 선택하고 정렬을 하면 된다.

 정렬은 상단 리본 메뉴의 [정렬 및 필터 -> 사용자 지정 정렬]을 클릭.

 정렬 기준은 분리한 문자와 숫자가 있는 셀을 선택하면 된다.

 정렬을 하려고 하면 정렬 경고가 나타나는데 [일반 숫자와 텍스트로 저장된 숫자를 모두 숫자로 정렬]을 선택하면 된다.

 

매크로를 이용한 방법

 아래의 매크로는 문자와 숫자가 섞인 셀을 정렬하는 매크로이다.

 다만 모든 상황에 사용이 가능한 것은 아니고 문자가 먼저 나오고 숫자가 나오는 경우에만 정상적으로 정렬이 되며 숫자가 먼저 나오거나 문자1, 숫자2, 문자3가 있거나 하다면 앞부분의 문자1, 숫자2 부분까지만 정렬되며 문자3 부분은 제대로 정렬되지 않는다.

 또한 정렬이라고는 해도 정렬을 위해 값을 덧씌우기 때문에 원본은 따로 복사 후 적용해 보고 원하는 결과가 나오는지 확인해 보는 것을 추천한다.

 (매크로로 변경된 데이터는 [Ctrl + Z]로 되돌리기가 안된다.)

 조건에 맞는다면 사용해 보자.

 

1. vba 실행.

 엑셀에서 [ALT + F10]을 눌러 VBA 실행하자.

 

2. 새 모듈 만들기.

 vba 창이 열리면 상단 메뉴에서 [삽입 -> 모듈]을 클릭하여 새 모듈을 만든 뒤 아래의 코드를 붙여 넣자.

Sub SortMixedValues()  Dim selectedRange As Range  Set selectedRange = Application.Selection  Dim numRows As Integer  numRows = selectedRange.Rows.Count  Dim values() As String  ReDim values(numRows)  Dim i As Integer  For i = 1 To numRows  values(i) = selectedRange.Cells(i, 1).value  Next i  Dim temp As String  Dim j As Integer  Dim k As Integer  For j = 1 To numRows - 1   For k = j + 1 To numRows    If Group(selectedRange.Cells(j, 1).value) = Group(selectedRange.Cells(k, 1).value) Then     If NumericValue(selectedRange.Cells(j, 1).value) > NumericValue(selectedRange.Cells(k, 1).value) Then      For x = 1 To selectedRange.Columns.Count       temp = selectedRange.Cells(j, x).value       selectedRange.Cells(j, x).value = selectedRange.Cells(k, x).value       selectedRange.Cells(k, x).value = temp      Next x     End If    ElseIf Group(selectedRange.Cells(j, 1).value) > Group(selectedRange.Cells(k, 1).value) Then     For x = 1 To selectedRange.Columns.Count      temp = selectedRange.Cells(j, x).value      selectedRange.Cells(j, x).value = selectedRange.Cells(k, x).value      selectedRange.Cells(k, x).value = temp     Next x    End If   Next k  Next j End Sub Function Group(value As String) As String  Dim result As String  result = ""  Dim i As Integer  For i = 1 To Len(value)   If IsNumeric(Mid(value, i, 1)) Then    Exit For   Else    result = result & Mid(value, i, 1)   End If  Next i  Group = result End Function Function NumericValue(value As String) As Long  If Len(value) = 0 Then   NumericValue = -1   Exit Function  End If  Dim result As Long  result = 1  Dim i As Integer  For i = Len(value) To 1 Step -1   If IsNumeric(Mid(value, i, 1)) Then    result = result * 10 + CLng(Mid(value, i, 1))   End If  Next i  NumericValue = result End Function

3. 매크로 실행.

 정렬을 하고자 하는 셀을 선택한 뒤 매크로를 실행하면 된다.

 정렬을 할 때는 가장 앞에 있는 셀을 기준으로 하게 되며 그 뒤에 셀은 기준 셀과 같이 정렬이 된다.

 B2부터 E7셀을 범위로 선택하고 매크로를 실행하면 B2 기준으로 정렬이 되며 나머지 C2부터 D7셀은 따라서 정렬이 된다.

 

그 외 내용

 매크로는 작성 후 테스트 했지만 간단히 해봤기 때문에 버그가 있을 수 있다.

 반드시 원본은 복사해 두고 테스트 후 잘 적용된다면 사용하는 것이 좋다.