convert IP address to XXX.XXX.XXX.XXX

G

Guest

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

Guest

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
 
B

Bob Phillips

Won't work, IP addresses already have the dot, but not necessarily leading
zeroes as the OP wants.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top