いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)IPアドレスのゼロ埋め/ゼロ削除

 [※2022年11月更新]
 Excelで、IPv4アドレスのゼロ埋め(ゼロパディング)と余分なゼロの削除(ゼロサプレス)をする方法です。
 新関数のTEXTSPLIT関数を使う方法と、FILTERXMLを使う少し変わった方法を紹介します。

  • TEXTSPLIT/TEXTJOIN関数等を使ってIPアドレスのゼロ埋めと余分なゼロの削除をすることができます。
  • FILTERXML/TEXTJOIN関数等を使う方法もあります。

手順

ゼロ埋めをする場合

TEXTSPLIT/TEXTJOIN関数を使う方法

 画像ではB3:B7にいくつかのIPアドレスが記録されています。
 これに対しC3セルに次のような式を入力し、さらに下方にコピーしています。
 0が補われ、数字が3桁ずつに揃えられているのがわかります。

 C3セル

=TEXTJOIN(".",,TEXT(TEXTSPLIT(B3,"."),"000"))

 TEXTSPLIT関数により「.」を区切り文字として文字列を(4つに)分割し、それぞれにTEXT関数を適用して3桁表記の形に変換し、最後にTEXTJOIN関数を使って「.」を挿んで結合しています。
 以前は文字列の分割がとても面倒でしたが、TEXTSPLIT関数が導入されたおかげで非常にシンプルに実現できます。
 なおTEXTJOIN関数の第2引数は省略しており、TRUEにしたときと同じく空白を無視するという設定になります。

FILTERXML関数を使う方法

 FILTERXML関数を使う変わり種の方法です。
 かなり長い式になりますが、TEXTSPLIT関数がないやや古いバージョンのExcelでも実現できます。

 対象データも結果も上記と全く同じとなっており、C3セルの式の内容は次のとおりです。

 C3セル

=TEXTJOIN(".",,TEXT(FILTERXML(SUBSTITUTE("<r><n>"&B3&"</n></r>",".","</n><n>"),"//n"),"000"))

 SUBSTITUTE関数を使ってアドレス中の「.」をnというタグに置き換え、FILTERXML関数を使って分割し(TEXTSPLIT関数の代用です)、TEXT関数でそれぞれ3桁に直し、最後にTEXTJOIN関数で再度「.」を加えつつ連結しています。

余分なゼロを削除する場合

TEXTSPLIT/TEXTJOIN関数を使う方法

 C3セルに次のような式を入力し、さらに下方にコピーしています。
 IPアドレスの余分な0(各オクテットの先頭の0及び00)が削除されているのがわかります。

 C3セル

=TEXTJOIN(".",,VALUE(TEXTSPLIT(B3,".")))

 TEXTSPLIT関数により「.」を区切り文字として文字列を分割し、それぞれにVALUE関数を適用して数値に変換し(ここで余分な0が消える)、最後にTEXTJOIN関数で「.」を挿んで結合しています。

FILTERXML関数を使う方法

 C3セル

=TEXTJOIN(".",,VALUE(FILTERXML(SUBSTITUTE("<r><n>"&B3&"</n></r>",".","</n><n>"),"//n")))

 SUBSTITUTE関数を使ってアドレス中の「.」をnというタグに置き換え、FILTERXML関数を使って分割し、VALUE関数でそれぞれ数値に置き換え(ここで余分な0が消える)、最後にTEXTJOIN関数で再度「.」を加えつつ連結しています。