Getrennte Oktette der IP-Adresse mithilfe von Formeln

Hier sind vier Formeln, die Sie brauchen,

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))

Die SUCHEN-Funktion ein drittes argument mit dem Namen start. So finden Sie die zweite Dezimalstelle, FINDEN Sie einen dezimal-Punkt, sondern Sie beginnen dort, wo der erste Dezimalpunkt plus eins. Es wäre, wie dies

=FIND(".",A1,4)

Starten Sie in 4, weil Ihre erste Komma ist in position 3. Aber Sie wissen das nicht, so haben Sie, um zu berechnen, '4'.

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

Nun um 4, finden wir die erste dezimal und geben Sie 1. Das ist nicht schade zu finden, die zweite. Aber die Dritte, die Sie haben zu gehen, eine andere Ebene. Und der vierte ist noch eine andere Ebene. Es ist schwer zu Lesen und schwer zu pflegen.

Machen es ein wenig leichter, können Sie mit hilfsspalten.

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)

Diese Weise tun Sie Ihre Funde in C und unter Bezugnahme auf jene zahlen in A.

Wenn Sie nicht wie hilfsspalten, und ich weiß nicht, könnten Sie schreiben, eine UDF wie

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

und damit können Sie Formeln wie diese

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)

Nicht so sauber wie die hilfsspalte, aber in sich geschlossene und auf jeden Fall besser als die eingebaute zu FINDEN.

Anderen UDF könnten Sie schreiben, Duplikate, was die VBA-split-Funktion hat.

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

Die Formel sieht aus wie

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

Wie gezeigt, in Frage, Zeile 1 sind die IP-Adressen (IPv4), ausgehend von A1.

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

Lösung

• 1. Teil (Zelle A2): =VALUE( LEFT(SUBSTITUTE(A1, ".", " "), 3 ))
• 2. Teil (Zelle A3): =VALUE( MID(SUBSTITUTE(A1, ".", " "), 8, 5 ))
• 3. Teil (Zelle A4): =VALUE( MID(SUBSTITUTE(A1, ".", " "), 15, 7))
• 4. Teil (Zelle A5): =VALUE(RIGHT(SUBSTITUTE(A1, ".", " "), 3 ))

Füllen Sie die Formeln nach rechts.

Hinweis: Sie können TRIM(...) statt VALUE(...) wenn Sie möchten, das Ergebnis im text-format.

Bonus: eine Einzige Formel zu bekommen formatiert die IP-Adresse

Können wir einen formatierte IP-Adresse 001.002.003.004 durch die folgende Formel.

= 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")

Erklärung

Durch SUBSTITUTEing der Punkt . mit 6 Räumen, erhalten wir:

                   |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===

• Charakter 1-3 enthält, und enthält nur den ersten Teil.
• Charakter 8-12 enthält, und enthält nur den zweiten Teil.
• Charakter 15-21 enthält, und enthält nur den Dritten Teil.
• Rechten 3 Zeichen enthält, und enthält nur den vierten Teil.