VBAでのReplace関数の使い方について

スポンサーリンク

内容を置き換えるPeplace関数

あるセルと他のセルの内容を比較しようとしたときに、セルの内容が微妙に違うせいで比較できないことがあります。

私はこの前、二つの表に書いてある店舗一覧を住所の情報を用いてVlookup関数で一つの表になるように加工していたのですが、住所の書き方が微妙に違っていて、情報を抜き出すのに苦労しました。

住所のハイフンが色々使われていて、データが一致している情報が探せなかったのが原因です。
同じ住所でもデータを作成した人によって色々な書き方があります。

  • 「○○市××8-1-1」
  • 「○○市××8 – 1 – 1」
  • 「○○市××8―1―1」
  • 「○○市××8-1-1」
  • 「○○市××8ー1ー1」
  • 「○○市××8丁目1番地1」
  • 空白が入っていたり、半角だったり、全角だったり、ひかれているハイフンがハイフンでなく横線だったりすると、それだけでVlookupでは探せなくなります。またハイフンではなく、「丁目」「番地」と記載されている場合もあります。

    色々な形式で記載されている住所を、一括である決まった形式に置き換えるときに便利な関数がReplace関数です。

    今回はVBAでのReplace関数の使い方について紹介します。

    VBAでのReplace関数の使い方

    VBAでのReplace関数の基本的な使い方は以下のようになります。

    Replace(元の文字列, 検索する文字列, 置き換える文字列)

    例としてA1セルの文字列のうち”―”を”-“に変更するには以下のように記載します。

    Cells(1, 1) = Replace(Cells(1, 1), “―”, “-“)

    私が作成したVBAコード

    Replace関数をもとにして私が住所一覧表を修正するのに作ったVBAコードは以下のようになります。

    Sub 住所修正()
    Dim i As Long
    i = 2
    Do Until Cells(i, 1) = “”
    Cells(i, 1) = Replace(Cells(i, 1), “―”, “-“) ’全角「―」を半角「-」に変換する
    Cells(i, 1) = Replace(Cells(i, 1), “-”, “-“) ’全角のマイナス「-」を「-」に変換する
    Cells(i, 1) = Replace(Cells(i, 1), “ー”, “-“) ’「ー」を「-」に変換する
    Cells(i, 1) = Replace(Cells(i, 1), “丁目”, “-“) ’「丁目」を「-」に変換する
    Cells(i, 1) = Replace(Cells(i, 1), “番地”, “-“) ’「番地」を「-」に変換する
    Cells(i, 1) = Replace(Cells(i, 1), ” “, “”) ’半角の空白を削除する
    Cells(i, 1) = Replace(Cells(i, 1), “ ”, “”) ’全角の空白を削除する
    i = i + 1
    Loop
    End Sub

    これで同じ書式で表されるようになり住所の突合せでの確認ができるようになりました。
    他に数字やアルファベットの半角、全角を変更する関数が必要な場合もあります。

    Excelのワークシート上で使用するREPLACE関数とは異なる

    Excelのワークシート上で使用するワークシート関数でもREPLACE関数はありますが、VBAで使用するReplace関数とは引数の設定が異なります。
    Excelのワークシート関数の中のREPLACE関数は以下のように引数を設定します。

    REPLACE(文字列, 開始位置, 文字数, 置換文字列)

    VBAの関数とは異なり、開始位置と文字数を設定して置換する方法になります。よって今回のような文字数がばらばらの住所一覧からハイフンの形をそろえたりはできず、少し使い勝手が悪い気がします。

    おすすめ書籍 amazonicon Amazon rakutenicon 楽天 Yahooicon Yahoo検索
    amazoniconAmazon
    rakutenicon楽天Kobo電子書籍ストア
    続 Excel VBAのプログラミングのツボとコツがゼッタイにわかる本【電子書籍】[ 立山秀利 ]
    価格:2,527
    rakutenicon楽天ブックス
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本(続) 最初からそう教えてくれればいいのに! Excel [ 立山秀利 ]
    価格:2,808
    rakuteniconbookfan 2号店 楽天市場店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本/立山秀利【1000円以上送料無料】
    価格:2,376
    rakutenicon楽天ブックス
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 最初からそう教えてくれればいいのに! Excel [ 立山秀利 ]
    価格:2,376
    rakuteniconHMV&BOOKS online 1号店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 最初からそう教えてくれればいいのに!Excel2007 / 2003対応 / 立山秀利 【本】
    価格:2,376
    rakuteniconブックオフオンライン楽天市場店
    【中古】 Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 最初からそう教えてくれればいいのに!Excel2007/2003対応 /立山秀利【著 【中古】afb
    価格:1,320
    rakuteniconKSC
    『中古』続ExcelVBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:6,989
    rakutenicon楽天Kobo電子書籍ストア
    デバッグ力でスキルアップ! Excel VBAのプログラミングのツボとコツがゼッタイにわかる本【電子書籍】[ 立山秀利 ]
    価格:2,236
    rakuteniconぐるぐる王国DS 楽天市場店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:2,376
    rakuteniconぐるぐる王国 楽天市場店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:2,376
    rakuteniconネットショップ駿河屋 楽天市場店
    【中古】単行本(実用) ≪コンピュータ≫ Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 / 立山秀利【中古】afb
    価格:1,570
    rakuteniconECJOY!プレミアム楽天市場店
    秀和システム Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 最初からそう教えてくれればいいのに! 立山秀利/著
    価格:2,376
    rakutenicon楽天Kobo電子書籍ストア
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本【電子書籍】[ 立山秀利 ]
    価格:2,138
    rakuteniconクロネコ書店
    【中古】Excel VBAのプログラミングのツボとコツがゼッタイにわかる本—最初からそう教えてくれればいいのに!Excel2007/2003対応【中古】
    価格:3,330
    rakuteniconコンプリートブックス
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:2,376
    rakuteniconbookfan 1号店 楽天市場店
    【店内全品5倍】Excel VBAのプログラミングのツボとコツがゼッタイにわかる本/立山秀利【3000円以上送料無料】
    価格:2,376
    rakuteniconドラマ楽天市場店
    【新品】【本】Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 立山秀利/著
    価格:2,376
    rakutenicon楽天Kobo電子書籍ストア
    続々 Excel VBAのプログラミングのツボとコツがゼッタイにわかる本【電子書籍】[ 立山秀利 ]
    価格:2,430
    rakuteniconKSC
    『中古』ExcelVBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:6,468
    rakuteniconKSC
    『中古』続々ExcelVBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:10,788
    rakuteniconドラマ楽天市場店
    【新品】【本】Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続 立山秀利/著
    価格:2,808
    rakuteniconbookfan 2号店 楽天市場店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続/立山秀利【1000円以上送料無料】
    価格:2,808
    rakuteniconbookfan 1号店 楽天市場店
    【店内全品5倍】Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続/立山秀利【3000円以上送料無料】
    価格:2,808
    rakuteniconぐるぐる王国DS 楽天市場店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続
    価格:2,808
    rakuteniconVALUE BOOKS
    【ポイント 10倍】【中古】Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 最初からそう教えてくれればいいのに! Excel 続 /秀和システム/立山秀利 (単行本)【年末 セール SALE 対象商品】
    価格:758
    rakuteniconVALUE BOOKS
    【中古】Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 最初からそう教えてくれればいいのに! Excel 続 /秀和システム/立山秀利 (単行本)【年末 セール SALE 対象商品】
    価格:758
    rakuteniconぐるぐる王国 楽天市場店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続
    価格:2,808
    rakuteniconECJOY!プレミアム楽天市場店
    秀和システム Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続 最初からそう教えてくれればいいのに! 立山秀利/著
    価格:2,808
    rakuteniconコンプリートブックス
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続
    価格:2,808
    rakuteniconブックオフオンライン楽天市場店
    【中古】 続ExcelVBAのプログラミングのツボとコツがゼッタイにわかる本 最初からそう教えてくれればいいのに! /立山秀利【著】 【中古】afb
    価格:1,264
    Yahooicon京都大垣書店Yahoo!店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続 / 立山 秀利 著
    価格:2,808
    Yahooiconbookfanプレミアム
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続 / 立山秀利
    価格:2,808
    Yahooiconbookfan Yahoo!店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続/立山秀利
    価格:2,808
    Yahooiconbookfanプレミアム
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 / 立山秀利
    価格:2,376
    Yahooiconbookfan Yahoo!店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本/立山秀利
    価格:2,376
    YahooiconHonya Club.com Yahoo!店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続/立山秀利
    価格:2,808
    Yahooiconぐるぐる王国 ヤフー店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続
    価格:2,808
    YahooiconHMV&BOOKS online Yahoo!店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 最初からそう教えてくれればいいのに!Excel2007 /
    価格:2,376
    Yahooiconぐるぐる王国 スタークラブ
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続
    価格:2,808
    Yahooiconぐるぐる王国2号館 ヤフー店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続
    価格:2,808
    Yahooiconぐるぐる王国DS ヤフー店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続
    価格:2,808
    Yahooiconぐるぐる王国 ヤフー店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:2,376
    Yahooiconぐるぐる王国 スタークラブ
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:2,376
    Yahooiconぐるぐる王国2号館 ヤフー店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:2,376
    Yahooiconぐるぐる王国DS ヤフー店
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本
    価格:2,376
    Yahooiconドラマ書房Yahoo!店
    新品本/Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続 立山秀利/著
    価格:2,808
    YahooiconドラマYahoo!店
    新品本/Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 続 立山秀利/著
    価格:2,808
    YahooiconWINDY BOOKS on line
    Excel VBAのプログラミングのツボとコツがゼッタイにわかる本  続
    価格:2,808
    Yahooiconドラマ書房Yahoo!店
    新品本/Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 立山秀利/著
    価格:2,376
    YahooiconドラマYahoo!店
    新品本/Excel VBAのプログラミングのツボとコツがゼッタイにわかる本 立山秀利/著
    価格:2,376

    スポンサーリンク

    シェアする

    • このエントリーをはてなブックマークに追加

    フォローする