View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
sebastienm sebastienm is offline
external usenet poster
 
Posts: 694
Default convert IP address to XXX.XXX.XXX.XXX

Since each segment (xxx) maybe 1, 2 , or 3 digits, you can use a new column
where you re-format the ip for the sole pupose of sorting: 000-000-000-000

Say your ip is in column A starting in row 2:
- insert new blank columns B,C,D,E
- in B2: =FIND(".",A2) <-- finds the first dot in the ip
- in C2: =FIND(".",A2,B2+1) <-- finds the second dot
- in D2: =FIND(".",A2,C2+1) <-- finds the 3rd dot
- in E2:
=TEXT(LEFT(A2,B2-1),"000")&"-"&TEXT(MID(A2,B2+1,C2-B2),"000")&"-"&TEXT(MID(A2,C2+1,D2-C2),"000")&"-"&TEXT(RIGHT(A2,LEN(A2)-D2),"000")
-- reformats ip as 000-000-000-000
- Copy/paste these formulas down along the ip data
- sort by column E

Regards,
Sebastien
"AVBBEN" wrote:

I need to convert a list of ~1000 IP address to XXX.XXX.XXX.XXX format in
order to sort them. Can anyone help?