• ベストアンサー

ExcelでIPアドレスをオクテット毎に分割したい

お世話になります。 Excelシート上のA列にIPアドレスが並んでおり、 第1オクテットから第4オクテットは3桁までの数で それぞれ"."で区切られています。 各オクテットの数値をB列～E列に分けて入れるには B列～E列にどのような関数を入れたらよいでしょうか、お知恵を拝借したく　お願いいたします。 ↓理想図です ------------------------------ A　　　　　　　　|　 B　|　C　|　D　|　E　 | ------------------------------ 1.2.3.4　　　　　 |　1　|　2　|　3　|　4　| ------------------------------ 10.5.100.6　　　|　10 |　5　| 100|　6　| ------------------------------ 101.22.7.102　 | 101|　22 |　7　| 102| ------------------------------ 33.8.103.104　 |　33 |　8　| 103| 104| ------------------------------

質問者が選んだベストアンサー

  • ベストアンサー
  • mshr1962
  • ベストアンサー率39% (7418/18948)
回答No.3

A列をB列にコピー 「データ」「区切り位置」で「カンマと...」を選んで「次へ」 「その他」の欄に「.」を入れて「完了」という方法もあります。 数式で行うなら B2=LEFT(A2,FIND(".",A2)-1) C2=MID(LEFT(A2,FIND(".",A2,LEN(B2)+2)-1),LEN(B2)+2,3) D2=MID(LEFT(A2,FIND(".",A2,LEN(B2&C2)+3)-1),LEN(B2&C2)+3,3) E2=RIGHT(A2,LEN(A2)-LEN(B2&C2&D2)-3)

linen3
質問者

お礼

ご回答ありがとうございます！ 区切り位置で分けるのが簡単のようですね。 関数もご教授いただきありがとうございました。 No.1の回答者様にも即座にご回答いただき、大変感謝しております。今後は必要に応じて使い分けたいと思います。 皆様ありがとうございました。

その他の回答 (3)

  • cockerel
  • ベストアンサー率46% (253/548)
回答No.4

＞どうもエレガントではありません。。。。。 エレガントではありませんが、ダミーは不要です。B1、C1、D1、E1にそれぞれ次の式を入れます。もう少しきれいにいきたいところですが・・・ =TRUNC(SUBSTITUTE(SUBSTITUTE(A1,".",,3),".",,2)) =TRUNC(SUBSTITUTE(SUBSTITUTE(A1,(B1&"."),,1),".",,2)) =TRUNC(SUBSTITUTE(A1,(B1&"."&C1&"."),,1)) =TRUNC(SUBSTITUTE(A1,(B1&"."&C1&"."&D1&"."),,1)) 最後のTRUNCは成形のためだけに使用しています。

linen3
質問者

お礼

ありがとうございました。 これは、、、全然考え付かなかったです！ というか貼り付けたら見事に成功しましたが仕組みはよくわかってないです。 これから分析します。。。 皆様すごいです！

  • otoutann
  • ベストアンサー率26% (248/933)
回答No.2

私はダミーの列が必要な場合は別シートで計算する事にしています。 そのシートを隠しておけば、見た目だけはエレガントです。

  • otoutann
  • ベストアンサー率26% (248/933)
回答No.1

１回こっきりの作業でしたら、一度テキストファイルで保存して、 そのテキストファイルをエクセルで開く時に、セパレーターを "."にすれば数字をセル毎に分ける事はできますが。

linen3
質問者

お礼

回答ありがとうございます。 なるほど～簡単でした！ ただ、作業は今後何回も発生すると予想されるので、関数では何かないでしょうか・・・・・・。 RIGHT,LEFT,FIND,LEN関数を組み合わせ、 ダミー列を何列か挿入してなんとかできるのですが、 どうもエレガントではありません。。。。。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • Excel IPアドレスを比較してセルを入替える

    以下のようなデータ 　　A列　　　　　B列 192.170.1.1 192.171.1.1 192.171.1.2 192.170.1.2 IPアドレスの第２オクテッドを比較し数値の小さいほうをが左にくるように VBAでクリック釦を作成したいのですがどのように記述すればよいでしょうか？ 釦をクリックすると 　　A列　　　　　B列 192.170.1.1 192.171.1.1 192.170.1.2 192.171.1.2 のように該当のセルがA列・B列入れかえができる。 IPアドレスを取得する関数は ユーザー関数を使用してsplitn(範囲、第○オクテッド）で 第２オクテッドの数値は取得できます。 よろしくお願いします。

  • エクセルの関数での質問です

    エクセルの関数での質問です sheet1に A　B　 C　 D　 E 1　○　○　○　1 2　○　○　○ 3　○　○　○　2 4　○　○　○ みたいな状態からsheet2に A　B　 C　 D　 E 1　○　○　○　1 3　○　○　○　2 のようにE列の数値順に行を並べることはできますか？ エクセル初心者なのでわかりやすく教えていただけると助かります。

  • excelでデータを別シートに引っ張ってきたい

    excelで値を検索し、返す時、返す値の横にあるデータを違うシートのセルの下に一緒に引っ張ってきたいときの関数（方法）を教えてください。 素人なので分かりにくいと思いますが、お知恵をお借りしたいと思います。 あるデータのシートの検索値を別シートに持ってきたいのですが、シート1をシート2のようにしたいのです。 ／は空白 例）シート１ ／A B C D E １(1) □ ○ ▽ ◎ 2 (2) ▼ ■ ◇ ◎ シート2 ／ A B C D 1 (1) □ 2 ／○ ▽ ◎ 3 (2) ▼ 4 ／■ ◇ ◎ 分かりにくいかもしれませんが、 シート１の(1)（A1）を検索すると、シート2（B1）に□（これはVLOOKで引っ張ってこれるのはわかります。） 次にシート1の（C1）○をシート2の（B2）へ持ってきたいのです。シート1（D1）（E1）はシート2の（C2）（D2）へ。 しかもシート1のC列は空白セルもあり、シート1のC列が空白の場合、下にずれることなくシート1の（D1）（E1）はシート2の（C1）（D1）へ。下記＜図a＞のようになるようにしたいのです。 そして、シート2のA列に検索値として入力する(1)や(2)の値は連番ではなく、(2)の次に(5)に飛んだりします。(2)の行のC列にデータがあっても、(5)のC列にデータはないこともあります。＜図ｂ＞ ＜図a＞ ／A B C D 1 (1) □ ▽ ◎ 2 (2) ▼ ◇ ◎ ＜図ｂ＞ ／ A B C D 1 (1) □ 2 ／○ ▽ ◎ 3 (2) ▼ 4 ／■ ◇ ◎ 5 (5) □ ▽ ◎ 6 (7) ▼ ◇ ◎ vlookとかCLUMN関数とか考えてはみたのですが、どうもうまくいきません。 毎回作成するデータなのですが、毎回コピペで作成しています。 とても面倒なので(1)を検索したらデータが一瞬で検索できるようにしたいと試みてはみたものの、私の知識では不可能でした。 関数では無理なのでしょうか。。。 関数はあまり詳しくないので分かりやすい方法があれば、教えていただければ助かります。 関数に詳しい方、よろしくおねがいいいたします。 関数にはこだわっていません。違う方法があればそれも含めておねがいいたします。

  • エクセルの関数について

    エクセルのA列とB列にはランダムな10桁の数値が入力されています。 A列の数値がB列にも有る場合、C列に“OK”と表示させたいのですが、C列にどのような関数を入れたら良いのか分かりません。どなたかご存知の方おしえて下さい。

  • エクセルで数値と文字が入ったセルからの数値の抽出

    皆さんこんにちは。 エクセルの使い方についての質問です。 添付画像の例のようにA列に 「XXYYYZZ(XとZは文字、Yは数値で3桁または4桁)」というフォーマットで 数値と文字が入っているセルがあります。 同様にB列には「WWZ(Wは数値)」というフォーマットで、 同様に数値と文字が入っているセルが並んでいます。 これらからDやE列にあるように数値のみを取り出すためには DやE列にどのような関数を入れてやればよいのでしょうか。 どなたか教えていただければ幸いです。 よろしくお願いします。

  • 数値を分割したい

    関数初心者です。 セルにさまざまな桁数の数値が入っています。 これを一字ずつ分割して別のセルに入れたいと考えています。 A1に1000が入っていたら、F1に1、G1～I1に0を A2に25369だったら、E2に2、F2に5、G2に3、H2に6、I2に9 というように、 B～Iの各セルに、後ろ詰め(という表現が適切かどうかわかりませんが)に入れたいのです。 A列に入っている数値は上限8桁ですが、3桁のこともあれば、8桁のこともある、と バラバラです。 よい方法がありましたら、ぜひお教えください。 お願いします。

  • エクセル関数

    エクセル初心者につき、質問させていただきます。 集計（抽出でしょうか）に関し シートAには A列に個々の件番（数値6桁です）があります 同一件番が複数あったり、一つだけだったりと 不規則です。 B列にA列の件番に対しての項目（文字列）があります 　A列　　　　B列 100001　　　電車 100001　　　バス 100001　　　徒歩 100003　　　徒歩 100006　　　電車 100006　　　バス これらのデータをシートBにて A列に6桁の数値を入力したら B・C・D列に以下の通り反映されるように したいのですがうまくいきません 　A列　　　　B列　　　C列　　　D列 100001　　　電車　　 バス　　 徒歩 100003　　　　　　　　　　　　　 徒歩 100006　　　電車　　 バス 適した関数を御教え頂ければありがたくお願いいたします。

  • ExcelでのINDIRECT関数の使い方について

    Excelの関数についてお伺いしたいと思います。現在、図のような表があり、A2, C2, E2を合計したいと思っております。条件として； 1) 列を頻繁に挿入するものの、常にA2, C2, E2を合計したい（セルがずれてしまっては困る） 2) B列やD列には他の数値が入るため（列が連続しない）、A2:E2ということができない これを解決するためにA8に"A2,C2,E2"と入力し、A9に=SUM(INDIRECT(A8)と記入したのですが、#REF!というエラーになってしまいます。この場合、どのようにIndirect関数を使えばよいのでしょうか。またそもそもIndirect以外の有効な関数を使うべきなのでしょうか。教えていただきたく、よろしくお願い致します。

  • EXCELで　一桁の数値を二桁に

    教えてください。 エクセルで、入っているデータを編集しているのですが A列には、一桁の数値が B列には、１桁から２桁の数値が C列には、１桁から２桁の数値が D列には、文字データが入っています。 Ｄ列のデータに対する対応表を作るために このＡ～Ｃ列のデータを CONCATENATE関数を使って合体させたいのですが、 （合体させると、数値がダブらない為） B列とC列に入っている1桁の数値を 2桁に変換したいのですが、 何かよい方法はありませんでしょうか？ 手打ちで変換するには、かなり多いデータなのです。 どなたか、助けてください。

  • エクセルで数値の上３桁で切捨てができる関数

    エクセルで、上３桁で切捨てできる関数はありますでしょうか？ エクセルで、たとえば1,234　54,321　678,901　と桁数が異なる 数値がランダムにある場合に、上３桁どりで、1,230　54,300 678,000　と返してくれる関数です。　 お知恵を拝借したくよろしくお願いいたします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する