Separate octets of IP address using formulas

Question

I want to separate out the octets of an IP address using Formulas.

I have tried some things like substitute & find but cannot figure this out.

Example of what I want to achieve, by only starting with Cell A1 and Cell B1:

10.17.9.192 | 192.168.0.1
10          | 192
17          | 168
9           | 0
192         | 1

Answer 1

Here is classic one formula solution:

=TRIM(MID(SUBSTITUTE(A$1,".",REPT(" ",999)),(ROW()-1)*999-998,999))

"." - is the delimiter.
(ROW()-1) - gives nth item in delimited text.

More info at EXCELFOX