Share via

Isolate last octet in IP address

Anonymous
2015-02-20T16:58:10+00:00

I need to list the last octet of an IP address in one column of excel.

If A1 has the IP address, I would like A2 to show the last octet of the IP address in A1.

I tried this, but when the last octet was only 2 digits, it gave me .xx instead of just xx.  It would probably do similar when the last octet is 1 digit.

=RIGHT(A1,FIND("~",SUBSTITUTE(A1,".","~",1)))

Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2015-02-20T17:07:35+00:00

Use

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

instead.

Was this answer helpful?

10+ people found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2015-02-20T17:08:50+00:00

Or Try this one:

=RIGHT(SUBSTITUTE(A1,".","~",3),LEN(SUBSTITUTE(A1,".","~",3))-FIND("~",SUBSTITUTE(A1,".","~",3)))

Was this answer helpful?

7 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-02-20T17:05:36+00:00

    Use this one.

    =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,".","~",3)))

    OR

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

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2015-02-20T17:04:12+00:00

    Hi,

    Try this

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

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2015-02-20T22:55:24+00:00

    Thanks for the feedback.  I realized later I could shorten my offering just a little and uncomplicated it a bit:

    =RIGHT(SUBSTITUTE(A1,".","~",3),LEN(A1)-FIND("~",SUBSTITUTE(A1,".","~",3)))

    And since if there is no entry in A1 or if it is not a properly entered IP address it will return an error, you can cover the error this way:

    =IFERROR(RIGHT(SUBSTITUTE(A1,".","~",3),LEN(A1)-FIND("~",SUBSTITUTE(A1,".","~",3))),"Not a valid IP")

    Was this answer helpful?

    0 comments