How to order ip-ranges for IPsec?

[mummiepoep]

Goodmorning!

Can anyone help me with the following problem?
See the picture, please ...

\1

I would like to close my server for specific IP-ranges as I am getting thousands of log-in attempts every day on my server from "bad" or even not existing organisations.
There are lists of IP-ranges of each country. Unfortunately the ranges are divided in thousands of small parts.
Now I would like to set the ranges in a numeric order but therefore many rows have to be filled up with zero's. Otherwise Excel is not able to set the rows in a useful order!

I am not sure this is possible inside Excel with some cell formula. I know a very little about trim, but this seems more complicated.

It has to be something like ...

Start at the left site
Count till the first point
If there are less than 3 digits add zero's until there are 3 (do not count the spaces!)
Go further to the next point
And so on.

As there are thousands of rows I am not able to do this by hand.

Anyone a good idea?

Thank you very much for your appreciated help!

[shg]

Welcome to the forum.

If you want help with Excel, please post workbooks, not pictures. When you do that, please show what result your trying to get to for some sample data.

[mummiepoep]

Here is a small example file of 20 rows.
The first 2 rows have been changed to the preferred "look".

Thanks for any help!

[shg]

If you're comfortable with a VBA solution, put this is a code module (explained below) ...

Please Login or Register  to view this content.

.. then in D1, =PadNums(A1, 3) and copy down.

Then copy col D, paste as values over col A, and delete col D.


Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel

[mummiepoep]

@ shg

Wow, this looks very prof ...
I will try this out and whatever the result ...
thanks for your great help.

But I will let you know if it works! However that may take one or two days.

Thanks again!

[mummiepoep]

@ shg

I am very sorry but it did not work!

For instance (using the updated 20 rows file) I typed in the cell D1 the value =PadNums(A1), in D2 the value =PadNums(A2).
And so on.
It even took away the manual added zero's!
Are you able to use the updated file with the 20 records and place this on the forum?

Thanks again for your appreciated help!

[mummiepoep]

Bump ... if it is allowed sometimes

[shg]

=PadNums(A1, 3)