【Excel関数】IPv6アドレスの省略表記にゼロを補完する【No VBA】

##概要

IPv6の省略形から、ゼロを補完した完全体に変換したい。
VBAを使わずExcel関数だけで、なおかつ途中計算用のセルをなるべく作らずに実現することを目指す。

https://www.nic.ad.jp/ja/newsletter/No46/0800.html

##下準備

IPv6アドレスを入力するセルに、【addr】と名前を定義する。
セルに名前をつけることで、セル番地に関わらず常に一定のセルを指定することができる。つまり、セルの名前さえ正しく定義すれば、下記のコードをどのファイルに貼り付けてもそのまま使える。

※Excelの「名前の定義」については下記を参照のこと。

https://qiita.com/AnPanda/items/c548c7a1db4cbc281fde

##コード

###Ver.1
コロンコロンを展開し、16bit fieldごとに列記、最後にtextjoinするパターン。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/464218/247b47a5-f394-5420-46d7-7fa7f5035c7e.png)

#####addr2

連続したゼロを省略する"::"を、":0"に変換する。"::"がなければ、なにもしない。
省略のないIPv6アドレスだと、":"の数は7つ。
"::"で省略されるfieldは、8から":"の数を引いたもの。
ex1) 2001::1　→コロン2個、省略field6個
ex2) 2001:2:3:4:5::1　→コロン6個、省略field2個
ex3) 2001:2:3:4:5:6::1　→コロン7個、省略field1個　※推奨される書き方かは不明。
ex4) 2001:2:3:4:5:6:7:1　→コロン7個、省略field0個　※"::"がないので、省略なしと判断。
`LEN(addr)-LEN(SUBSTITUTE(addr,":",""))`で":"を数え、省略されたfieldの数だけREPTで":0"を繰り返し、最後に":"をつける。
ただし、元のアドレスの末尾が"::"や"::/xx"だった場合は、ゼロを補って":0"にする必要がある。

```
=SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&IF(OR(COUNTIF(addr,"*::"),COUNTIF(addr,"*::/*")),":0",":"))
```

#####field1

fieldの頭のゼロを補完する。最初の":"より手前の文字数を調べ、文字数が4になるようにREPTでゼロを追加する。

```
=REPT(0,4-LEN(LEFT(addr2,FIND(":",addr2)-1)))&LEFT(addr2,FIND(":",addr2)-1)
```

#####field2

1個目の":"を開始位置にして、2個目の":"の手前までをMIDで取得する。
n個目の":"の位置を把握するには、FINDとSUBSTITUTEを組み合わせて使う。元の文字列(addr2)のn回目に出てきた":"をSUBSTITUTEでダミー文字(Θ)に置換してから、ダミー文字をFINDで検索することで、n回目に出てきた":"の位置を割り出すことができる。
2個目の":"の位置から1個目の":"の位置を引いてfield2の文字数を調べ、field1と同様にREPTでゼロを追加する。
(以下、field7までは同様)

```
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))-1)
```

#####field3

```
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-1)
```

#####field4

```
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-1)
```

#####field5

```
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-1)
```

#####field6

```
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-1)
```

#####field7

```
=REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-1)
```

#####field8

元の入力値にプレフィックス(/64とか)がついていたりいなかったりすることを想定し、IFで分岐。
プレフィックスありなら、"/"の位置を調べてその手前までの文字数に対してREPTでゼロ付加してから、RIGHTで取得した"/"以降を末尾に付加する。
プレフィックスなしなら、MIDの文字数をfieldの最大値4にする。

```
=IF(COUNTIF(addr2,"*/*"),REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,FIND("/",addr2)-FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-1)))&MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,FIND("/",addr2)-FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-1)&RIGHT(addr2,LEN(addr2)-FIND("/",addr2)+1),
REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,4)))&MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,4))
```

###Ver.2

コロンコロンを展開するために、1セルだけ処理用セルを作成したパターン。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/464218/ce94667f-645a-82cd-2532-3d6fa1ae8ac2.png)

#####addr2

Ver.1と同じ。

```
=SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&IF(OR(COUNTIF(addr,"*::"),COUNTIF(addr,"*::/*")),":0",":"))
```

#####結果を出力するセル

Ver.1で各セルに分けたfieldを、":"でつないですべて単一のセルに入れた形。
(2186文字)

```
=REPT(0,4-LEN(LEFT(addr2,FIND(":",addr2)-1)))&LEFT(addr2,FIND(":",addr2)-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",1))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",2))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",3))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",4))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",5))-1)&":"
&REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))+1,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-FIND("Θ",SUBSTITUTE(addr2,":","Θ",6))-1)&":"
&IF(COUNTIF(addr2,"*/*"),REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,FIND("/",addr2)-FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-1)))&
MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,FIND("/",addr2)-FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))-1)&RIGHT(addr2,LEN(addr2)-FIND("/",addr2)+1),
REPT(0,4-LEN(MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,4)))&MID(addr2,FIND("Θ",SUBSTITUTE(addr2,":","Θ",7))+1,4))
```

###Ver.3

コロンコロンの処理(addr2)も無理矢理ひとつのセルにねじ込んでみたパターン。
7218文字。<font color=red>**該当セルでF2押すだけでExcelが数秒間操作不能(場合によっては落ちる)**</font>になったので、実用的ではない。
※field8が"::"のときの挙動が正確でないことが判明。修正しようと思ったがExcel操作不能になるので未対応。

```
=REPT(0,4-LEN(LEFT(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND(":",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-1)))&LEFT(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND(":",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",1))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",1))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",1))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",1))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",2))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",3))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",4))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",5))-1)&":"
&REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))-1)))&
MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))+1,FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",6))-1)&":"
&IF(COUNTIF(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),"*/*"),REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))+1,FIND("/",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))-1)))&MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))+1,FIND("/",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))-1)&RIGHT(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),LEN(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))-FIND("/",SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"))+1),
REPT(0,4-LEN(MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))+1,4)))&MID(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),FIND("Θ",SUBSTITUTE(SUBSTITUTE(addr,"::",REPT(":0",8-(LEN(addr)-LEN(SUBSTITUTE(addr,":",""))))&":"),":","Θ",7))+1,4))
```

##まとめ

* ゼロ補完の逆、ゼロ省略も関数でやってみたくなった。
* 関数の処理を解説するのは大変(今回はほぼ放棄してる)。
* 関数込みで7000文字超をひとつのセルに入れると、処理が滞る。
* なんでもひとつのセルに入れればいいってものでもない。途中計算の結果を適宜別セルに記載することで、可読性の向上やバグフィックスに役立つ。