概要
IPv6の省略形から、ゼロを補完した完全体に変換したい。
VBAを使わずExcel関数だけで、なおかつ途中計算用のセルをなるべく作らずに実現することを目指す。
下準備
IPv6アドレスを入力するセルに、【addr】と名前を定義する。
セルに名前をつけることで、セル番地に関わらず常に一定のセルを指定することができる。つまり、セルの名前さえ正しく定義すれば、下記のコードをどのファイルに貼り付けてもそのまま使える。
※Excelの「名前の定義」については下記を参照のこと。
コード
Ver.1
コロンコロンを展開し、16bit fieldごとに列記、最後にtextjoinするパターン。
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セルだけ処理用セルを作成したパターン。
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文字。該当セルでF2押すだけでExcelが数秒間操作不能(場合によっては落ちる)になったので、実用的ではない。
※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文字超をひとつのセルに入れると、処理が滞る。
- なんでもひとつのセルに入れればいいってものでもない。途中計算の結果を適宜別セルに記載することで、可読性の向上やバグフィックスに役立つ。