4

I'm trying to create an automated function that will determine the class identification for IP addresses after you input it into Column A's input section by checking the first section of the address. Example:

Column A  | Column B

10.250.1.1 | Class A

(The input section is where the address is.)

It would do this by checking the first part of the address (10).250.1.1 and matching it to the class. The classes range like this:

Class A = 1 through 127

Class B = 128 through 191

Class C = 192 through 223

So another example would be

Column A  | Column B

194.250.1.1 | Class C

I had tried to create an IF statement in Column B that would determine which range the initial part of the IP address would fit into IE: 58.250.1.1 And it would figure that 58 fits into Class A since it is between 1 and 127. However I'm unsure on how to separate the "58" from the whole IP, I know how to compare it by using the whole IP but not just a section of it. –

Dave
  • 25,513
David
  • 43

4 Answers4

3

You can do this with a single, fairly straightforward formula. For IP address in A1, use the following:

=INDEX({"Class A","Class B","Class C"},MATCH(VALUE(LEFT(A1,FIND(".",A1)-1)),{1,128,192,224},1))

Breaking it down:

VALUE(LEFT(A1,FIND(".",A1)-1)) uses string functions to identify the first part of the address and cast it as a number, not a string.

This number is then matched to the array of class lower bounds {1,128,192,224}. MATCH with last argument 1 returns the last position in this array that is less than the number, e.g., 25 would return 1, 150 would return 2.

This position is then passed to the INDEX function which returns the value at that position in the array of categories {"Class A","Class B","Class C"}.

This formula will return #N/A for values less than 1 and #REF! for values greater than 223.

Excellll
  • 12,847
1

This does what you want, if VBa is OK?

So, you can see the start and end values. This is the range for each of the 3 classes (hence the A, B and C). Then you can see classA, classB and classC - I kept these incase you needed to change the value for any reason. Last is, what column does the IP live under (the columnToLookUp and lastly which column do you want the results. I've chosen A and B respectively). 

Option Explicit
Sub DoThis()

'Edit this top part as you need

Dim startClassA As Integer
startClassA = 1

Dim endClassA As Integer
endClassA = 127

Dim startClassB As Integer
startClassB = 128

Dim endClassB As Integer
endClassB = 191

Dim startClassC As Integer
startClassC = 192

Dim endClassC As Integer
endClassC = 223

Dim classA As String
classA = "Class A"

Dim classB As String
classB = "Class B"

Dim classC As String
classC = "Class C"

Dim columnToLookUp As String
columnToLookUp = "A"

Dim resultColumn As String
resultColumn = "B"

'no need to edit below this (hopefully) ***************

Dim row As Integer
row = 1

Do While (Range(columnToLookUp & row).Value <> "")
    Dim ip() As String

    ip = Split(Range(columnToLookUp & row).Value, ".")

    Dim ipSub As Integer
    ipSub = ip(0)

    If (ipSub >= startClassA And ipSub <= endClassA) Then
        Range(resultColumn & row).Value = classA
    End If

    If (ipSub >= startClassB And ipSub <= endClassB) Then
        Range(resultColumn & row).Value = classB
    End If

    If (ipSub >= startClassC And ipSub <= endClassC) Then
        Range(resultColumn & row).Value = classC
    End If


row = row + 1
Loop


End Sub

Also, may be helpful: How do I add VBA in MS Office?

Community
  • 1
Dave
  • 25,513
0

You can do it using the LOOKUP function. The syntax is

LOOKUP(lookup_value, lookup_vector, [result_vector])

The nice thing about LOOKUP is, if LOOKUP can't find the exact lookup_value, it matches the largest value in the lookup_vector that is less than or equal to the lookup_value.

The picture below shows a couple of ways to do it, in columns D and E, using helpers in columns B and C.

enter image description here

F106dart
  • 1,873
0

VBA is great for advanced functions but comes at a price, your code now includes macro and unless you have a digital certificate microsoft is going to hammer you with security messages. If the sheet is only for you you can disable that but it will make sharing your sheet with other a nightmare.

Fortunately, for your problem there is a simpler solution in pure Excel:

Considering the following arrangement:

  • IP adresses in column A
  • First byte of IP address in column B
  • Result Class in column C

table

The formula in column B would be:

=VALUE(LEFT(A1,FIND(".",A1,1)-1))

The formula in column C would be:
=IF(B1<128,"Class A",IF(B1<192,"Class B",IF(B1<256,"Class C","Not a valid IP")))

If you are not bothered about readability, you can even avoid the helper column: short table

By using in column B:

=IF(VALUE(LEFT(A1,FIND(".",A1,1)-1))<128,"Class A",IF(VALUE(LEFT(A1,FIND(".",A1,1)-1))<192,"Class B",IF(VALUE(LEFT(A1,FIND(".",A1,1)-1))<256,"Class C","Not a valid IP")))

Hoki
  • 101