엑셀에서 IP Address 의 끝자리 분리하기

보통 엑셀에서 IP Address 정보를 불러와서 작업해야될 경우가 있다. 해본 사람들은 알겠지만, 엑셀에서 IP Address 를 정렬하면, 원하던 결과가 안나온다.
즉, 예를 들어 아래의 데이터를 정렬 하면, 10.1.1.1 다음으로 10.1.1.2 가 아닌 10.1.1.10 이 온다. 

10.1.1.1
10.1.1.10
10.1.1.11
10.1.1.12
10.1.1.2
10.1.1.3
10.1.1.4
10.1.1.5
10.10.10.10
10.10.10.11
10.10.10.18
10.10.10.19
10.10.10.20
10.10.10.21
10.10.10.22
10.100.100.1
10.100.100.2
10.100.100.3
10.100.100.4
10.100.100.5
10.100.100.6
100.100.100.100
100.100.100.101
100.100.100.102
100.100.100.103
100.100.100.104

사실 이게 보통 짜증나는 일이 아닐 수 없다.


이제부터 이걸 제대로 정렬을 할 방법을 알아 보려 한다.
방법은 간단하다, 맨 마지막 자리의 데이터를 다른 열로 옮긴후, 해당열을 기준으로 정렬을 하면 끝난다. ^^

자 그럼 맨 마지막 자리의 데이터(즉 10.1.1.x, 10.1.1.xx, 10.1.1.xxx 에서의 x, xx, xxx 값들)를 어떻해 다른 열로 옮길 것인가가 문제이다.

이를 위한 방법은 문자열 분리에 있다.
문자열 분리를 위해 필요한 함수는 left(), mid(), right(), find() 이 네가지만 있으면 된다.
이 함수들의 사용법에 대해서는 별도 설명하지 않겠다.(내용이 너무 길어져서 ....)

자 지금부터 하려고 하는 아래의 결과값을 먼저 보자

즉, A열의 원래값에서 맨마지막 값만 따로 분리를 해서 B열과, C 열 로 나눈 것이다.

A열	B열	C 열
10.1.1.1	10.1.1.	1
10.1.1.10	10.1.1.	10
10.1.1.11	10.1.1.	11
10.1.1.12	10.1.1.	12
10.1.1.2	10.1.1.	2
10.1.1.3	10.1.1.	3
10.1.1.4	10.1.1.	4
10.1.1.5	10.1.1.	5
10.10.10.10	10.10.10.	10
10.10.10.11	10.10.10.	11
10.10.10.18	10.10.10.	18
10.10.10.19	10.10.10.	19
10.10.10.20	10.10.10.	20
10.10.10.21	10.10.10.	21
10.10.10.22	10.10.10.	22
10.100.100.1	10.100.100.	1
10.100.100.2	10.100.100.	2
10.100.100.3	10.100.100.	3
10.100.100.4	10.100.100.	4
10.100.100.5	10.100.100.	5
10.100.100.6	10.100.100.	6
100.100.100.100	100.100.100.	100
100.100.100.101	100.100.100.	101
100.100.100.102	100.100.100.	102
100.100.100.103	100.100.100.	103
100.100.100.104	100.100.100.	104

휴, 다시 보니 끔찍하기만 하다. ^^ 저걸 하기 위한 여러가지 방법들이 있겠으나, vbscript 를 짜서 할수도 있을것이고....
하지만, 그냥 함수만을 사용해서 해보려는 것이다.
물론, left((), mid(), right() 함수를 쭉쭉 써서 어떻해 어떻해 하면 나오긴 하겠지만, 생각보다 함수가 엄청 길어진다.....

좀더 쉽게 생각하는 방법을 알아보자
분리해야될 포인트는 점(.) 이다. 이 점(.)은 총 세개가 있다. 각각의 자리위치값(자릿수)를 구하면 좀더 쉽게 구할 수 있다.

A열 1행의 값(10.1.1.1)을 기준으로 하고, 첫번째 점(.)의 자리위치값 을 구해보자.

=Find(".",A1)                # a1 셀값(10.1.1.1) 에서 왼쪽부터 점(.)의 위치를 찾아라

첫번째 점(.)의 위치값은 3일 것이다.

A열 1행의 값(10.1.1.1)을 기준으로 하고, 두번째 점(.)의 자리위치값을 구해보자

=FIND(".",A1,FIND(".",A1)+1)                   # 첫번째 점(.)의 위치 다음부터 (find(".",a1)+1) 점(.)의 위치를 찾아라

두번째 점(.)의 위치값은 5 일 것이다.

A열 1행의 값(10.1.1.1)을 기준으로 하고, 세번째 점(.)의 자리위치값을 구해보자

=FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)              # 두번째 점(.)의 위치 다음 
                                                                                       (FIND(".",A1,FIND(".",A1)+1)+1) 점

                                                                                                   (.)의 위치를 찾아라

세번째 점(.)의 위치값은 7 일 것이다.

여기서 정말 필요한 값은 세번째 점의 위치값(=7) 이다.

그럼 이제 B열의 값을 구해보자. 기준은 A1 이다

=LEFT(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1))          # A1 셀값에서 왼쪽부터 7번째 자리까지의

       문자열

이 값은 10.1.1. 이 될 것이다.

C열의 값을 구해보자. 기준은 A1 이다.

=Right(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1))          # A1 셀값 우측에서부터 1번째 자리까지의

                    문자열

여기서의 핵심은 마지막 점(.) 다음부터 새로운 열(C열)로 표기하는 것이다. 즉, 전체 문자열길이에서 마지막 세번째 점(.)까지의 문자열의 길이를 뺀 나머지 만큼만 전체 문자열을 기준으로 우측에서부터 구하면 되는 것이다.
즉 이 값은 1 이 될 것이다.

자 분리는 끝났다. 이제 해야 될 일은 C열을 기준으로 정렬만 하면 된다. ^^