Separate octets of IP address using formulas

Question

I want to separate out the octets of an IP address using Formulas.

I have tried some things like substitute & find but cannot figure this out.

Example of what I want to achieve, by only starting with Cell A1 and Cell B1:

10.17.9.192 | 192.168.0.1
10          | 192
17          | 168
9           | 0
192         | 1

Solution 1

Here are four formulas you need

A2:=LEFT(A1,FIND(".",A1)-1)
A3:=MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)
A4:=MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-(FIND(".",A1,FIND(".",A1)+1)+1))
A5:=MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1))

The FIND function has a third argument named start. So to find the second decimal point, you FIND a decimal point but you start where the first decimal point is plus one. It would be like this

=FIND(".",A1,4)

You start in 4 because your first decimal point is in position 3. But you don't know that, so you have to compute the '4'.

=FIND(".",A1,FIND(".",A1)+1)

Now to get 4, we find the first decimal and add 1. That's not too bad to find the second. But to find the third, you have to go another level. And the fourth is yet another level. It's hard to read and hard to maintain.

To make it a little easier, you can use helper columns.

A2        =LEFT(A1,C2-1)
C2        =FIND(".",A1)
A3        =MID(A1,C2+1,C3-C2-1)
C3        =FIND(".",A1,C2+1)
A4        =MID(A1,C3+1,C4-C3-1)
C4        =FIND(".",A1,C3+1)
A5        =MID(A1,C4+1,LEN(A1)-C4-1)

That way you're doing your FINDs in C and referring to those numbers in A.

If you don't like helper columns, and I don't, you could write a UDF like

Public Function FINDi(find_text As String, within_text As String, Optional instance As Long) As Long

    Dim lReturn As Long
    Dim i As Long

    Const lFINDFIRST As Long = 0

    If instance = lFINDFIRST Then
        lReturn = InStr(1, within_text, find_text)
    ElseIf instance < lFINDFIRST Then 'negative numbers finds last
        lReturn = InStrRev(within_text, find_text)
    Else
        lReturn = 0
        For i = 1 To instance
            lReturn = InStr(lReturn + 1, within_text, find_text)
        Next i
    End If

    FINDi = lReturn

End Function

and that gives you formulas like this

A2        =LEFT(A1,findi(".",A1)-1)
A3        =MID(A1,findi(".",A1)+1,findi(".",A1,2)-findi(".",A1,1)-1)
A4        =MID(A1,findi(".",A1,2)+1,findi(".",A1,3)-findi(".",A1,2)-1)
A5        =MID(A1,findi(".",A1,3)+1,LEN(A1)-findi(".",A1,3)-1)

Not as clean as the helper column, but self contained and definitely better than the built-in FIND.

Another UDF you could write duplicates what VBA's split function does.

Public Function SplitString(ByVal sInput As String, ByVal sDelim As String, ByVal lWhich As Long) As String

    SplitString = Split(sInput, sDelim)(lWhich - 1)

End Function

That formula looks like

A2        =SplitString($A$1,".",ROW()-1)
A3        =SplitString($A$1,".",ROW()-1)
A4        =SplitString($A$1,".",ROW()-1)
A5        =SplitString($A$1,".",ROW()-1)

Solution 2

Here is classic one formula solution:

=TRIM(MID(SUBSTITUTE(A$1,".",REPT(" ",999)),(ROW()-1)*999-998,999))

"." - is the delimiter.
(ROW()-1) - gives nth item in delimited text.

More info at EXCELFOX

Solution 3

As shown in question, row 1 are the IP addresses (IPv4), starting from A1.

  |      A      |      B
--+-------------+-------------
1 | 10.17.9.192 | 192.168.0.1
2 | 10          | 192
3 | 17          | 168
4 | 9           | 0
5 | 192         | 1

Solution

• 1st part (cell A2): =VALUE( LEFT(SUBSTITUTE(A1, ".", " "), 3 ))
• 2nd part (cell A3): =VALUE( MID(SUBSTITUTE(A1, ".", " "), 8, 5 ))
• 3rd part (cell A4): =VALUE( MID(SUBSTITUTE(A1, ".", " "), 15, 7))
• 4th part (cell A5): =VALUE(RIGHT(SUBSTITUTE(A1, ".", " "), 3 ))

Fill the formulas to the right.

Note: You can use TRIM(...) instead of VALUE(...) if you want the result in text format.

---

Bonus: Single formula to get formatted IP address

We can get a formatted IP address 001.002.003.004 by the following formula.

= TEXT( LEFT(SUBSTITUTE(A1, ".", "      "), 3    ), "000") & "."
& TEXT(  MID(SUBSTITUTE(A1, ".", "      "), 8, 5 ), "000") & "."
& TEXT(  MID(SUBSTITUTE(A1, ".", "      "), 15, 7), "000") & "."
& TEXT(RIGHT(SUBSTITUTE(A1, ".", "      "), 3    ), "000")

---

Explanation

By SUBSTITUTEing the dot . with 6 spaces, we get:

                   |123456789|123546789|123456789|
1.1.1.1         ->  1      1      1      1
11.11.11.11     ->  11      11      11      11
111.111.111.111 ->  111      111      111      111
                    =1=    ==2==  ===3===

• Character 1-3 contains and only contains the first part.
• Character 8-12 contains and only contains the second part.
• Character 15-21 contains and only contains the third part.
• Rightmost 3 characters contains and only contains the fourth part.

Comments

[Poof]

can you show an example of what you have and what you want to achieve ?

[Luke Wolfenden]

Edited to show what I mean.

[hoss]

I have time for a hint; =MID(A1,SEARCH(".",A1)+1, SEARCH(".",A1,SEARCH(".",A1)+1-SEARCH(".",A1))-1) will give you the second octet.

[Luke Wolfenden]

Thanks hoss, although it works, I cannot make sense of it. Could you explain what it is doing there?

[Shrout1]

For the fourth octet if there is a CIDR value following use this formula (traps errors if there is no CIDR notation - no slash "/"): =IF(ISERROR(FIND("/",A5,1)),MID(A5,FIND(".",A5,FIND(".",A5,F‌​IND(".",A5)+1)+1)+1,‌​LEN(A5)-FIND(".",A5,‌​FIND(".",A5,FIND("."‌​,A5)+1)+1)),MID(A5,F‌​IND(".",A5,FIND(".",‌​A5,FIND(".",A5)+1)+1‌​)+1,((FIND("/",A5,FI‌​ND(".",A5,FIND(".",A‌​5,FIND(".",A5)+1)+1)‌​+1)+1)-1)-(FIND(".",‌​A5,FIND(".",A5,FIND(‌​".",A5)+1)+1)+1)))