使用公式分隔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 开始，单元格 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函数有一个名为的第三个参数。所以要找到第二个小数点，你找到一个小数点，但是从第一个小数点加上一个开始。这将是这样的

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

，并为您提供这样的公式

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)

不如帮助列那么干净，但自包含，绝对比内置的FIND更好。

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

另一个UDF你可以写出重复的VBA的拆分功能。

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屋！