Extract Numbers from an IP address

[swalker235]

Hi

I'm struggling to extract the last 1,2 or 3 digits from an IP address. the problem I have is that I have different length of IP address, so for example

10.1.93.145
145.10.5.16
145.168.192.1

I am trying to extract the numbers after the last full stop so the above example results would be 145, 16 and 1

I have tried using =RIGHT and FIND but I am struggling to get it to consistently pull out just the last numerical values.

Has anyone got any suggestions please?

 

[Special-K99]

Here ya go

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+1,LEN(A1))

Source: How to extract the last substring from a Excel column? - Stack Overflow

 

[VoG]

Try

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

 

[swalker235]

Both worked perfectly! Thank you so much.....I'm now going to try and unpick them to see if I can actually understand the forumula.....

Cheers both

 

[snb_]

or

=MID(A1,FIND("~",SUBSTITUTE(A1,".","~",3))+1,10)

 

[István Hirsch]

Or give this a try:

=LOOKUP(10^9,1*RIGHT(SUBSTITUTE(A1,".","x"),{1,2,3,4,5,6,7,8,9}))

 

[swalker235]

All of these solutions are working. Thanks for the quick response all.