いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

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

 Excelで、IPアドレスのゼロ埋め(ゼロパディング)と余分なゼロの削除(ゼロサプレス)をする方法です。
 ここでは少し変わったアプローチを試してみます。

  • FILTERXML関数等を使ってIPアドレスのゼロ埋めと余分なゼロの削除をすることができます。

手順

ゼロ埋めをする例

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

f:id:accs2014:20210228115615p:plain:w780

 C3セル

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

 ちょっと変わった方法ですが、SUBSTITUTE関数を使ってアドレス中の「.」をnというタグに置き換え、FILTERXML関数を使って分割し(つまりはSPLIT関数の代用です)、TEXT関数でそれぞれ3桁に直し、最後にTEXTJOIN関数で再度「.」を加えつつ連結しています。
 TEXTJOIN関数の第2引数は省略しています(このときTRUE=空白を無視する、と同じ)。

余分なゼロを削除する例

f:id:accs2014:20210228115621p:plain:w780

 C3セル

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

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