IP 주소를 정렬하는 방법 – 엑셀 및 구글 스프레드시트

이 튜토리얼에서는 엑셀 및 구글 스프레드시트에서 IP 주소를 정렬하는 방법을 보여드리겠습니다.

IP 주소 정렬하기

IP 주소는 점(마침표)으로 구분된 네 개의 숫자(각 숫자의 최대 길이는 세 자리)로 구성됩니다. 엑셀에서 이 형식으로는 데이터를 올바르게 정렬할 수 없습니다. 올바로 정렬하기 위해서는 먼저 네 개의 숫자가 모두 세 자리로 구성되도록 필요한 곳에 선행하는 0을 추가해야 합니다. 아래와 같은 IP 주소 목록이 있다고 가정해 보겠습니다.

이를 올바르게 정렬하려면 다음과 같은 단계를 따라야 합니다:

1. C1 셀에 아래와 같은 수식을 입력하여 IP 주소를 구성하는 각 숫자에 선행하는 0을 추가합니다 :

=TEXT(LEFT(B1,FIND(".",B1,1)-1),"000")&"."&TEXT(MID(B1,FIND(".",B1,1) 1,FIND(".",B1,FIND(".",B1,1) 1)-FIND(".",B1,1)-1),"000")& "."&TEXT(MID(B1,FIND(".",B1,FIND(".",B1,1) 1) 1,FIND(".",B1,FIND(".",B1,FIND(".",B1,FIND(".",B1,F1) 1) 1)-1),"000")&"."&TEXT(RIGHT(B1,LEN(B1)-FIND(".",B1,FIND(".",B1,FIND(".",B1,F1) 1) 1)),"000")

2. 채워져 있는 마지막 행(6행)까지 수식을 아래로 드래그해줍니다.

3. 이제 IP 주소에서 선행하는 0을 필요로 했던 모든 곳에 선행하는 0이 채워졌습니다. 정렬하기 전에 먼저 이들 값을 복사하여 붙여넣습니다. 수식이 있는 범위 (C1:C6)를 선택하고 선택한 영역을 마우스 오른쪽 버튼으로 클릭한 다음 복사를 선택합니다(또는 키보드 단축키 CTRL + C를 사용).

4. 다음 열(D1)의 첫 번째 셀을 마우스 오른쪽 버튼으로 클릭하고 값 붙여넣기 아이콘을 선택합니다(또는 단축키를 사용하여 값 붙여넣기).

5. 이제 수식이 포함된 열은 삭제하고 데이터를 정렬할 차례입니다. 서식이 적용된 데이터 범위 (C열)의 아무 곳이나 클릭하고 리본에서 홈 > 정렬 및 필터 > 텍스트 오름차순 정렬로 이동합니다.

그 결과 B열의 IP 주소가 올바르게 정렬됩니다. 이제 도우미 열(C)은 삭제하면 됩니다.

수식은 어떻게 작동하는가?

1단계에서 사용한 복잡한 수식은 TEXT, LEFT, MID, RIGHT 및 FIND 함수를 사용하여 네 개의 숫자 각각이 세 자리의 길이를 갖도록 선행하는 0을 추가합니다.

• FIND 함수는 IP 주소에서 점(마침표)을 찾습니다.
• LEFT, MID, RIGHT 함수는 각 숫자를 추출합니다.
• 마지막으로 TEXT 함수는 각 숫자의 길이가 세 자리(“000”)가 되도록 형식을 지정합니다. 즉, 한 자리 숫자 앞에는 0 두 개를, 두 자리 숫자 앞에는 0 하나를 추가해준다는 것입니다. 앰퍼샌드 (&)는 점으로 구분되어 있는 각각의 숫자를 결합하는 데 사용됩니다.

구글 스프레드시트에서 IP 주소 정렬하기

위의 1단계에서 사용한 동일한 수식으로 구글 스프레드시트에서도 IP 주소를 정렬할 수 있습니다.

1. C1 셀에 다음 수식을 입력하고 채워져 있는 마지막 행(6)까지 이를 드래그해줍니다.

=TEXT(LEFT(B1,FIND(".",B1,1)-1),"000")&"."&TEXT(MID(B1,FIND(".",B1,1) 1,FIND(".",B1,FIND(".",B1,1) 1)-FIND(".",B1,1)-1),"000")& "."&TEXT(MID(B1,FIND(".",B1,FIND(".",B1,1) 1) 1,find(".",B1,find(".",B1,find(".",B1,1) 1) 1)-find(".",B1,find(".",B1,1) 1)-1),"000")&"."&text(right(B1,len(B1)-find(".",B1,find(".",B1,find(".",B1,1) 1) 1)),"000")

2. 이제 IP 주소를 구성하는 숫자에는 선행하는 0이 있고 모두 세 자리가 되었습니다. 정렬하기 전에 값으로 복사하여 붙여넣습니다. 수식이 있는 범위 (C1:C6)를 선택하고 선택한 범위를 마우스 오른쪽 버튼으로 클릭한 다음 복사 (또는 Ctrl + C 사용)를 선택합니다.

3. 다음 열(D1)의 첫 번째 셀을 마우스 오른쪽 버튼으로 클릭하고 선택하여 붙여넣기를 클릭한 다음 값만 붙여넣기를 선택합니다(또는 CTRL + Shift + V 단축키 사용).

4. 이제 수식이 있는 열은 삭제하고 서식이 적용된 데이터를 정렬합니다. 서식이 적용된 데이터 범위(C열)의 아무 곳이나 클릭하고 메뉴에서 데이터 > 시트 정렬 > C열을 기준으로 시트 정렬 (오름차순)으로 이동합니다.

그 결과 B열의 IP 주소가 올바르게 정렬되며, 이제 도우미 열(C)은 삭제하면 됩니다.