IP Formatting Issues (asking for VBA help!)

[Rashie]

Hi All,

I have messed about with this for hours with zero progress so I'm going to open this up to the room in hopes somebody can be a lifesaver. I've taken on a project of updating IP ranges. I have close to about 2 million of these to work through and our system has generated the IP's in a format I can't use, nor can I have this amended.

As an example, the data has come through as the below.

193.49.125.33-46
195.220.131-133.0-24
89.83.109.192
91.212.128.130
195.85.247.1-254

This sadly isn't much use to me because of the way our system takes it in would be like this

193.49.125.33 - 193.49.125.46
195.220.131.0 - 195.220.133.24
89.83.109.192
91.212.128.130
195.85.247.1 - 195.85.247.254

To clarify each hyphen in the example IP ranges means "to." So with the second example, there were 2 separate hyphens for the range in the third and fourth block of numbers. There always needs to be a space, hyphen, space between the ranges for our horrible outdated system to accept them.

Hoping somebody can help point me in the right direction with a bit of VBA or let me know if more information is required.

Thanks for your help,

Rashie.

 

[bs0d]

Are you looking to list all IP's that match within hyphened range?

Or just convert "193.49.125.33-46" to "193.49.125.33 - 193.49.125.46"?

If you're just looking to convert as outlined above, you wouldn't need VBA to accomplish this. Here's a solution to consider. I'd perform a text-to-columns on the IP's, with "." as delimiter. Then setup some evaluation columns for the last two subnet groups that can have a hyphen. Allow two columns for each subnet. One column displays the values left of "-"; the other displays right of "-". In the last column, piece it all together with some logic. Here is an example:

F2:

=IFERROR(LEFT(C2, SEARCH("-", C2, 1)-1), C2)

G1:

=IFERROR(RIGHT(C2,LEN(C2) - SEARCH("-", C2)), "")

H2:

=IFERROR(LEFT(D2, SEARCH("-", D2, 1)-1), D2)

I2:

=IFERROR(RIGHT(D2,LEN(D2) - SEARCH("-", D2)), "")

Output (K2):

=IF(AND(G2="", I2=""), A2&"."&B2&"."&C2&"."&D2, A2&"."&B2&"."&F2&"."&H2&" - "&A2&"."&B2&"."&IF(G2="", F2&"."&I2, G2&"."&I2))