Search code examples
excelexcel-formulaexcel-2007delimiter

Split string at last (or fourth) occurence of "." delimiter

I like to delimit the string as follow

Given the following String :

Column 1    
10.80.111.199.1345
127.0.0.1.3279

I will like to delimit numbers after the last ".", which will get the follow output

 Column 1       Column 2               

10.1.12.5       1345
127.0.0.1       3279

I know excel has the delimitor function which allows me to delimit with specific symbol or through the fixed width. It does not seems to work for fixed width.

Is there any alternatives, rather than delimited with "." can concating back the strings on Column 1?

Solution

  • If all of your values follow the same format you have described then you could use these formulas:

    =LEFT(A1,LEN(A1)-5) returns "127.0.0.1"

    =RIGHT(A1,4) returns "3279"

    Otherwise, if it needs to be more dynamic then these formulas will work (Reference: Excel: last character/string match in a string)

    =LEFT(A1,FIND("@",SUBSTITUTE(A1,".","@",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1)

    returns "127.0.0.1"

    =TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",99)),99))

    returns "3279"