Remove the last octet from an IP Address

[meppwc]

I am trying to find a way of trimming away the last octet from an IP address in addition to the last period "."
The problem that I am running into is the last octet can be from 1 to 3 numbers in length

Example
10.127.17.1 -- result needed 10.127.17
10.127.17.22 -- result needed 10.127.17
10.127.17.454 -- result needed 10.127.17

 

[meppwc]

sorry............just figured it out

=LEFT(A1,FIND("~",SUBSTITUTE(A1,".","~",3))-1)

 

[User3001]

Thanks for your post. I iterated with your help and wanted to post what I came up with. Excuse the formatting, in a bit of a rush. I'll post a .png of the text and the text itself. If this solution is not completely correct, please post! If anyone wants to add the ones I am missing, that'd be great.

Input / Data: Output / Function: Function: What it does:
123.456.789.321 123.456.789 =LEFT(A2,FIND("~",SUBSTITUTE(A2,".","~",3))-1) Remove the last octet of the IP.
123.456.789.321 123.456 =LEFT(A3,FIND("~",SUBSTITUTE(A3,".","~",2))-1) Remove the last two octets of the IP.
123.456.789.321 123 =LEFT(A4,FIND("~",SUBSTITUTE(A4,".","~",1))-1) Remove the last three octets of the IP.
123.456.789.321 456.789.321 =RIGHT(A5,LEN(A5)-FIND("~",SUBSTITUTE(A5,".","~",1))-0) Remove the first octet of the IP.
123.456.789.321 789.321 =RIGHT(A6,LEN(A6)-FIND("~",SUBSTITUTE(A6,".","~",2))-0) Remove the first two octets of the IP.
123.456.789.321 321 =RIGHT(A7,LEN(A7)-FIND("~",SUBSTITUTE(A7,".","~",3))-0) Remove the first three octets of the IP.
123.456.789.321 789 =LEFT(RIGHT(A8,LEN(A8)-FIND("~",SUBSTITUTE(A8,".","~",2))-0),FIND(".",RIGHT(A8,LEN(A8)-FIND("~",SUBSTITUTE(A8,".","~",2))-0))-1) Show only the third octet.


Thanks again!

 

[Fluff]

With 365 you can do it like

Fluff (version 1).xlsb
	A	B
1
2	123.456.789.321	123.456.789
3	123.456.789.321	123.456
4	123.456.789.321	123
5	123.456.789.321	456.789.321
Sheet5

Cell Formulas
Range	Formula
B2	B2	=TEXTBEFORE(A2,".",-1)
B3	B3	=TEXTBEFORE(A3,".",-2)
B4	B4	=TEXTBEFORE(A4,".",-3)
B5	B5	=TEXTAFTER(A5,".",1)

 

[User3001]

With 365 you can do it like

Fluff (version 1).xlsb
	A	B
1
2	123.456.789.321	123.456.789
3	123.456.789.321	123.456
4	123.456.789.321	123
5	123.456.789.321	456.789.321
Sheet5

Cell Formulas
Range	Formula
B2	B2	=TEXTBEFORE(A2,".",-1)
B3	B3	=TEXTBEFORE(A3,".",-2)
B4	B4	=TEXTBEFORE(A4,".",-3)
B5	B5	=TEXTAFTER(A5,".",1)

That's easy! Thank you!