Skip to content

Instantly share code, notes, and snippets.

@JohnLBevan

JohnLBevan/ConvertCidrIpToInt.xlsx.md

Last active May 31, 2024 13:07
Show Gist options
  • Save JohnLBevan/ec7a830e41e43a6a0b5db9d053e08041 to your computer and use it in GitHub Desktop.
Save JohnLBevan/ec7a830e41e43a6a0b5db9d053e08041 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. JohnLBevan renamed this gist May 31, 2024. 1 changed file with 0 additions and 0 deletions.
    0 ConvertCidrIpToInt.xlsx.txt → ConvertCidrIpToInt.xlsx.md
    View file
    File renamed without changes.
  2. JohnLBevan created this gist May 31, 2024.
    19 changes: 19 additions & 0 deletions ConvertCidrIpToInt.xlsx.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    Row 1 has headings:
    - CIDR
    - IP
    - First IP Int
    - Last IP Int
    - Test IP Int
    - IP In Range

    Column A contains CIDRs (e.g. `3.2.1.0/30`)

    Column B contains IPs (e.g. `3.2.1.2`)

    Column C contains a formula to calculate the first IP in the CIDR: `=INDEX(TEXTSPLIT($A2, "."), 1) * 256^3 + INDEX(TEXTSPLIT($A2, "."), 2) * 256^2 + INDEX(TEXTSPLIT($A2, "."), 3) * 256 + INDEX(TEXTSPLIT($A2, {".","/"}), 4)`

    Column D contains a formula to calculate the last IP in the CIDR: `=C2 + POWER(2,32-INDEX(TEXTSPLIT($A2, {".","/"}), 5)) - 1`

    Column E contains a forumla to calculate the IP: `=INDEX(TEXTSPLIT(B2, "."), 1) * 256^3 + INDEX(TEXTSPLIT(B2, "."), 2) * 256^2 + INDEX(TEXTSPLIT(B2, "."), 3) * 256 + INDEX(TEXTSPLIT(B2, "."), 4)`

    Column F checks whether the IP in column B is in the CIDR from column A: `=AND($E2>=$C2,$E2<=$D2)`