If you find yourself with a spreadsheet containing hundreds of servers, and there’s an IP Address Column, you may want to create 4 additional columns, each containing each of the four octets of the IP Address.
This may be useful for grouping large numbers of hosts in a spreadsheet by subnet.
And this may be useful in grouping together servers in legacy infrastructure by application where that information doesn’t exist, since deployments of solutions across multiple servers are often grouped together in a range of IP addresses allocated to the projects by the Network admin.
These formulae are used to separate out each of the four octets.
A2:=LEFT(A1,FIND(".",A1)-1)
A3:=MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)
A4:=MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-(FIND(".",A1,FIND(".",A1)+1)+1))
A5:=MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1))Did you like this?
Tip cyberfella with Cryptocurrency
Tip cyberfella with Cryptocurrency
Donate Bitcoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some bitcoin:
Donate Bitcoin Cash to cyberfella
Scan the QR code or copy the address below into your wallet to send bitcoin:
Donate Ethereum to cyberfella
Scan the QR code or copy the address below into your wallet to send some Ether:
Donate Litecoin to cyberfella
Scan the QR code or copy the address below into your wallet to send some Litecoin:
Donate Monero to cyberfella
Scan the QR code or copy the address below into your wallet to send some Monero:
Donate ZCash to cyberfella
Scan the QR code or copy the address below into your wallet to send some ZCash:
I use Excels Text to Columns function, and specify the delimiter as . and /
play around with that until you get the result you want.
What if I needed to strip an ip with cidr into 4 octets and a 5th column containing the cidr? with the / and with out the /.
Example: 192.168.100.1/24
A1= 192.168.100.1/24
A2=192
A3=168
A4=100
A5=1
A6=/24
A7=24
=MID(A1;FIND(“/”;A1)+1;LEN(A1)-FIND(“.”;A1))