Function to add zeros to IP address data

[Kelsey Braun]

Hi all,
I work in a school and we have an excel spread sheet with many ip addresses
that have been input into it incorectly. To make it work i need to first
find the addresses without the zeros and add them where needed. For
example; the need to look like this -172.22.033.010 not like this
172.22.33.10 or this 172.22.33.010 or this 172.22.033.10. In other words
the last two octets have to have 3 digits. I would like to have a function
that will find the missing zeros and add them. I have been working on if
statements and the mid and replace functions.

Any help wold be appreciated,
Kelsey

 

[Dave Peterson]

Are the entries all in a single column?

If yes, then I'd insert 5 additional columns to the right of that column.

Then select the column
data|text to columns
delimited by period (.)

Now each of the octets are in its own column.

Then in that 5th column, you can join them together.
if your new columns are B:E, you could use a formula like:
=TEXT(B1,"000.")&TEXT(C1,"000.")&TEXT(D1,"000.")&TEXT(E1,"000")

(no dot in that last part!)

Then copy that formula down the column.

Select that column
edit|Copy
edit|paste special|Values
(and delete the original column plus 4 of the helper columns)