使用公式分隔 IP 地址的八位字节 [英] Separate octets of IP address using formulas

问题描述

我想使用公式将 IP 地址的八位字节分开.

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.

我想要实现的示例，仅从 Cell A1 和 Cell B1 开始:

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

推荐答案

这里有你需要的四个公式

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

FIND 函数有一个名为 start 的第三个参数.因此，要找到第二个小数点，您需要找到一个小数点，但从第一个小数点加一开始.应该是这样的

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)

你从 4 开始，因为你的第一个小数点在位置 3.但你不知道，所以你必须计算 '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)

现在要得到 4，我们找到第一个小数并加 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)

这样您就可以在 C 中进行 FIND 并在 A 中引用这些数字.

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

如果你不喜欢辅助列，而我不喜欢，你可以写一个像这样的 UDF

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

这给了你这样的公式

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)

不像辅助列那么干净，但自包含并且绝对比内置的 FIND 更好.

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

您可以编写的另一个 UDF 与 VBA 的 split 函数的功能相同.

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

那个公式看起来像

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

这篇关于使用公式分隔 IP 地址的八位字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！