Excel Formula to seperate date by a "." delimeter

Hello,

I am trying to separate data within a cell based on the "." delimeter.

I am using IP Addresses and am able to pull the 1st and second octet information with no problem. Where I am having problems is correctly pulling the 3rd octect information

Here is the formula I am using: =RIGHT(L2,LEN(L2) - SEARCH(".", L2, SEARCH(".", L2) + 1))

I just cant get the information to pull correctly!!!

I find the substitute combined with find the most helpfull to find 2nd to n-th occurrance.
only thing to keep in mind is to use a character that is real sure of not being part of any of the text strings to separate, in this case pipe-symbel will do

Formula:

Please Login or Register  to view this content.

Originally Posted by Roel Jongman

I find the substitute combined with find the most helpfull to find 2nd to n-th occurrance.
only thing to keep in mind is to use a character that is real sure of not being part of any of the text strings to separate, in this case pipe-symbel will do

Formula:

Please Login or Register  to view this content.

What am I doing wrong..??? This formula errors out...Error 2-11-19.PNG

sorry, I missed a semi colon as formula separator.. should have used a converter

try this one..

Formula:

Please Login or Register  to view this content.

Originally Posted by Roel Jongman

sorry, I missed a semi colon as formula separator.. should have used a converter

try this one..

Formula:

Please Login or Register  to view this content.

Ok, we are getting closer...this only works correctly if there is a 3 digit value in the 3rd octet. if there is a single digit or double digit value, it returns incorrect information.

For example if the IP Address is 192.168.99.100. I need it to return just 99

or if its 192.168.1.100. I need it to return 1

Originally Posted by AlKey

Enter in M2 and drag formula across to cell O2

Formula:

Please Login or Register  to view this content.

v	L	M	N	O
1	IP ADDRESS	1ST OCTET	2ND OCTET	3RD OCTET
2	192.168.100.212	192	168	100.212

OK, we are close....I only need to return the value in the 3rd octet which in this example is 100. Not 100.212

Originally Posted by shg

Why not just use text to columns with dot as the delimiter?

Because I need the sheet to be dynamic as its providing data to other sheets pulling information...

at M2
=MID(SUBSTITUTE($L2,".",REPT(" ",LEN($L2))),(COLUMN(A1)-1)*LEN($L2)+1,LEN($L2))
copy to right

Originally Posted by Ghozi Alkatiri

at M2
=MID(SUBSTITUTE($L2,".",REPT(" ",LEN($L2))),(COLUMN(A1)-1)*LEN($L2)+1,LEN($L2))
copy to right

BOOOOM!!!!! Works great!!!! Thank you very much and thanks to everyone that contributed! HUGE time saver and error reducer!!!!!!!!!!!!!!!