Extracting the 2nd and 3rd octet from an IP address

[hikerguy]

Hello,

Is there a one-line formula I can use to extract the 2nd and 3rd octet of an IP address? For example, if the IP was 192.168.30.1, I want to extract "168.30"
I've found formulas to do the first three or the last one, but not the middle two octets.

Thanks,

Andy

 

[Fluff]

How about
=TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,FIND(".",A2),""),".",REPT(" ",100),2),100))

 

[Rick Rothstein]

Another way...

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",20)),20,40))," ",".")

 

[hikerguy]

Perfect. Thank you both!!

 

[hikerguy]

Meant to ask what the numbers in the formulas mean (the 100, 20 and 40)?

 

[alansidman]

As an alternative, here is the Power Query Solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Column1.2.1", "Column1.2.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.1", "Column1.2.2"})
in
    #"Removed Columns"

 

[sandy666]

or

IP		IP
192.168.30.1		168.30

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TBD = Table.TransformColumns(Source, {{"IP", each Text.BetweenDelimiters(_, ".", ".", 0, 1), type text}})
in
    TBD

 

[Fluff]

Glad we could help & thanks for the feedback.