Excel VBA 電話番号からハイフンを削除し0から表示させる方法 | Evaluate&Replace

仕事でVBAを使うことがあり、やりたいことはネットで調べていますが、VBA初心者の私が分かりづらかったものを、初心者でも分かるように説明したいと思います。

効率的なコードが書けていない可能性もありますので、予めご了承ください。

今回はEvaluateとReplaceを使用し、電話番号など0から始まる数値データのハイフンを削除し、削除後も0から表示させる方法を紹介します。

使用ケース

以下のような顧客データがあり、別システムへデータを取り込む際に電話番号のハイフンを削除する必要がある、といったことが仕事上であるかと思います。

データが数件であれば自動化する必要もないと思いますが、その件数が大量で頻度も高い作業なのであれば、ある程度自動化できれば楽ですよね。

電話番号など0から始まる数字データのハイフンを削除すると、頭の0が表示されなくなってしまいます。

書式設定で頭の0を表示させることも可能ですが、データを上書き（例でいうと電話番号の変更など）すると、その書式は失われてしまいます。

なので、汎用性を高めるためにも、このような場合はハイフンを削除する前に、先頭にアポストロフィ（'）を付けることで解決することができます。

サンプルコード

Sub hyphendelete()

Dim LastRow
Dim cell As Range

LastRow = Cells(Rows.Count, 4).End(xlUp).Row

Sheets("sheet1").Range("D2:D" & LastRow).Value = Evaluate("""'""&D2:D" & LastRow)

Call Range("D2:D" & LastRow).Replace("-", "")

For Each cell In ActiveSheet.UsedRange
  cell.Errors(xlNumberAsText).Ignore = True
Next

End Sub

コード解説

LastRow = Cells(Rows.Count, 4).End(xlUp).Row

まずはハイフンを削除したいデータがある列の最終行を取得してしまいます。

例でいうと電話番号なので4行目となります。

Sheets("sheet1").Range("D2:D" & LastRow).Value = Evaluate("""'""&D2:D" & LastRow)

ハイフン削除する前に、対象列の全てのセル先頭にアポストロフィを追加します。

追加はEvaluateメソッドを使用します。EvaluateメソッドはExcelの数式を実行するための関数です。

今回はD2からD列の最終行までを、「Aから'A（アポストロフィ付）に変更しなさいよ」という数式を実行するようにしています。

"""'""&D2:D" の部分は少し分かりにくいですが、アポストロフィを""""（ダブルクォーテーション4つ）で囲んでいます。シート名は適時変更してください。

このアポストロフィを付けることによって、ハイフンが削除された後も頭の0が消えないようになります。

Call Range("D2:D" & LastRow).Replace("-", "")

Replaceメソッドを使用し、ハイフンをブランクへ置換しています。

Replace(What, Replacement, [LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat])

[]内は省略可能

• What：検索する文字列を指定します。
• Replacement：置換する文字列を指定します。
• LookAt：完全に一致するセルを検索するか指定します。
• SearchOrder：どの方向に検索するか指定します。
• MatchCase：大文字と小文字を区別するか指定します。
• MatchByte：半角と全角を区別するか指定します。
• SearchFormat：書式を検索条件にするか指定します。
• ReplaceFormat：書式を置換するか指定します。

ReplaceメソッドはExcelの置換ダイアログのオプションを引き継ぐため、マクロを実行するファイルで手動による置換を実行する場合は、引数の指定をしてください。

「call」については、なぜ必要か分かりませんでしたが、これがないと私の環境下ではエラーとなり作動しなかったため記述しております。

この辺りが説明できずもどかしいのですが、素人なのでお許しください。。。

For Each cell In ActiveSheet.UsedRange
  cell.Errors(xlNumberAsText).Ignore = True
Next

ここまででハイフン削除し0から表示させることができましたが、数値が文字列として保存されることで、セルにエラー表示（三角マーク）がでてしまいます。

表示されていても問題ないのですが見栄えが悪いので、エラーを無視する処理を実行しています。

エラー無視の処理はIgnore（イグノア）プロパティで行います。

Ignoreはエラーチェックオプションの状態の設定が可能であり、Falseで有効、Trueで無効となります。なので今回はTrueを設定します。

なかなかややこしいコードになってしまったので、分解して解説します。

• For Each：繰り返し処理を宣言。
• cell In ActiveSheet.UsedRange：アクティブシートのデータが入っているセル（UsedRange）を、変数cellで宣言。
• cell.Errors(xlNumberAsText)：宣言した範囲で、テキストとして入力された数字のエラーを指定。
• Ignore = True：エラーを無視するを実行。

これによりセルエラーも消え、目的通りの表示がされました。

あとは別システムに貼り付けするためにコピーまでを自動化したり、別のエクセルファイルへ書き出すなど、目的に応じ追加いただければと思います。

ユーザーフォームを使用した入力フォームの作り方

週ごとに縦に並んだ表を横並びにする方法 | Find&Set

セルに入力された日付を取得し自動でシートとファイルを作る方法

検索窓にキーワードを入力しEnterキーで検索するフォームの作り方 | Intersect

ブックを開いた時のウィンドウサイズを指定する方法 | workbook_open

新規ブックを作成しマクロが登録されたボタンを設置する方法 | OnAction

複数のワークシートをまとめて表示・非表示にする方法 | Visible Not.Visible

セル位置が変わっても特定の範囲を選択する方法 | Find&Address

電話番号からハイフンを削除し0から表示させる方法 | Evaluate&Replace

ダイアログボックスからファイルを選択して開く | GetOpenFilename