3

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?

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
user1004413
  • 2,509
  • 6
  • 23
  • 33

1 Answers1

16

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"

Jeroen Wiert Pluimers
  • 23,965
  • 9
  • 74
  • 154
dosdel
  • 1,118
  • 8
  • 8
  • +1 for mentioning https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string where @tigeravatar explains why that solution works. – Jeroen Wiert Pluimers Mar 25 '23 at 14:42