VBA_電話番号フォーマット成形(ハイフン付与)

電話番号のハイフンを、正確に付与するExcelのVBAコードだよん。
簡単に誰でも実行＆実装可能なExcelVBAでは作成している人見かけないし、いても文字数からハイフンを入れる方法は転がってるけど正確には付与されないし、有料なら販売しているけど無料でなんとかならないかなということから作成しちゃった！！

結構、電話番号のハイフン付与に苦労してる人多いのでは？

あーちなみに、ざっくり作成したので実務で使用するならキレイに色々修正してくださいな！


まずは、完成形サンプルコード！

Sub FormatHaihun()

    Dim ws As Worksheet ' 電話番号が記載されているシート
    Dim masterSheet As Worksheet ' 市外局番リストが記載されているシート
    Dim phoneColumn As Range ' 電話番号が含まれるA列全体の範囲
    Dim phoneNumber As String ' 電話番号
    Dim formattedNumber As String ' ハイフン付与電話番号
    Dim cityCode As String ' 市外局番
    Dim restNumber As String ' 市外局番を抜いた残りの番号
    Dim cell As Range ' 対象電話番号のセル
    
    Dim masterArray() As Variant ' 市外局番のマスタ配列
    Dim i As Long ' ループカウンタ
    Dim maxCityCodeLength As Long ' 市外局番の最大文字数

    ' データがあるシートと市外局番マスタのシートを定義
    Set ws = ThisWorkbook.Sheets("PhoneNumbers") ' 電話番号があるシート
    Set masterSheet = ThisWorkbook.Sheets("CodeMaster") ' 市外局番リストがあるシート

    ' B列をクリア
    ws.Columns("B").ClearContents

    ' ヘッダーを設定
    ws.Cells(1, "A").Value = "電話番号貼り付け"
    ws.Cells(1, "B").Value = "ハイフン付与電話番号"

    ' 電話番号と市外局番の範囲を定義
    Set phoneColumn = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    masterArray = masterSheet.Range("A1:A" & masterSheet.Cells(masterSheet.Rows.Count, "A").End(xlUp).Value)

    ' 市外局番リストの中で最大文字数を求める
    maxCityCodeLength = 0
    For i = LBound(masterArray, 1) To UBound(masterArray, 1)
    
        If Len(masterArray(i, 1)) > maxCityCodeLength Then
            maxCityCodeLength = Len(masterArray(i, 1))
        End If
        
    Next i

    ' 電話番号をフォーマットしてB列に出力
    For Each cell In phoneColumn
    
        ' 電話番号を文字列として取得し、全角から半角に変換
        phoneNumber = CStr(cell.Value)
        phoneNumber = StrConv(phoneNumber, vbNarrow) ' 全角を半角に変換

        ' 不要な記号を除去
        phoneNumber = Replace(phoneNumber, "(", "")
        phoneNumber = Replace(phoneNumber, ")", "")
        phoneNumber = Replace(phoneNumber, "-", "")

        cityCode = ""
        formattedNumber = ""

        ' 最大長から順に市外局番を検索
        For i = maxCityCodeLength To 1 Step -1
        
            For j = LBound(masterArray, 1) To UBound(masterArray, 1)
            
                If Len(masterArray(j, 1)) = i Then
                
                    If Left(phoneNumber, i) = masterArray(j, 1) Then
                        cityCode = masterArray(j, 1)
                        Exit For
                    End If
                    
                End If
                
            Next j
            
            If cityCode <> "" Then Exit For
            
        Next i

        ' 市外局番が見つかった場合、ハイフンを挿入
        If cityCode <> "" Then
        
            ' 市外局番の次の文字
            restNumber = Mid(phoneNumber, Len(cityCode) + 1)

            ' 市外局番 + 残りの番号を分割してハイフンを挿入
            If Len(restNumber) > 4 Then
                ' 市外局番 + 残りの番号-4文字 + 残り番号の右4文字
                formattedNumber = cityCode & "-" & Left(restNumber, Len(restNumber) - 4) & "-" & Right(restNumber, 4)
            Else
                formattedNumber = cityCode & "-" & restNumber
            End If
            
        Else
        
            ' 市外局番が見つからなかった場合はそのまま
            formattedNumber = phoneNumber
            
        End If

        ' フォーマット済みの番号をB列に書き込む
        cell.Offset(0, 1).Value = formattedNumber
        
    Next cell

    MsgBox "電話番号にハイフン付与完了"
    
End Sub

準備するシート

CodeMaster

・市外局番マスタ
　A列に市外局番全て記載

PhoneNumbers

・成形用シート
　A列に成形前電話番号
　B列に成形後電話番号

市外局番マスタ

▼総務省 ここから
▼電話帳ナビ ここから

上記どちらかから CodeMaster シートのA列に転記してください。

ちなみに下記は、面倒な人向けコピー用(※202411閲覧時)

市外局番	
0120	フリーダイヤル
0800	フリーダイヤル
050	IP電話
070	携帯電話
080	携帯電話
090	携帯電話
020	IoT/PHS（現在はIoT用途）
0570	ナビダイヤル
0990	テレドーム/情報提供ダイヤル
104	電話番号案内
110	警察
113	電話の故障受付
115	電報受付
116	加入電話の相談受付
117	時報
118	海上保安庁
119	消防
171	災害用伝言ダイヤル
177	天気予報
188	消費者ホットライン
189	児童相談所
011	
0123	
0124	
0125	
0126	
01267	
0133	
0134	
0135	
0136	
01372	
01374	
0137	
01377	
0138	
01392	
0139	
01397	
01398	
0142	
0143	
0144	
0145	
01456	
01457	
0146	
01466	
0152	
0153	
0154	
01547	
015	
0155	
01558	
0156	
01564	
0157	
0158	
01586	
01587	
0162	
01632	
01634	
01635	
0163	
0164	
01648	
0165	
01654	
01655	
01656	
01658	
0166	
0167	
0172	
0173	
0174	
0175	
0176	
017	
0178	
0179	
0182	
0183	
0184	
0185	
0186	
0187	
018	
0191	
0192	
0193	
0194	
0195	
019	
0197	
0198	
022	
0220	
0223	
0224	
0225	
0226	
0228	
0229	
0233	
0234	
0235	
023	
0237	
0238	
0240	
0241	
0242	
0243	
0244	
024	
0246	
0247	
0248	
025	
0250	
0254	
0255	
0256	
0257	
0258	
0259	
0260	
0261	
026	
0263	
0264	
0265	
0266	
0267	
0268	
0269	
0270	
027	
0274	
0276	
0277	
0278	
0279	
0280	
0282	
0283	
0284	
0285	
028	
0287	
0288	
0289	
0291	
029	
0293	
0294	
0295	
0296	
0297	
0299	
03	
0422	
042	
0428	
04	
043	
0436	
0438	
0439	
044	
045	
0460	
046	
0463	
0465	
0466	
0467	
0470	
047	
0475	
0476	
0478	
0479	
048	
0480	
049	
0493	
0494	
0495	
04992	
04994	
04996	
04998	
052	
053	
0531	
0532	
0533	
0536	
0537	
0538	
0539	
054	
0544	
0545	
0547	
0548	
0550	
0551	
055	
0553	
0554	
0555	
0556	
0557	
0558	
0561	
0562	
0563	
0564	
0565	
0566	
0567	
0568	
0569	
0572	
0573	
0574	
0575	
0576	
05769	
0577	
0578	
058	
0581	
0584	
0585	
0586	
0587	
059	
0594	
0595	
0596	
0597	
05979	
0598	
0599	
06	
072	
0721	
0725	
073	
0735	
0736	
0737	
0738	
0739	
0740	
0742	
0743	
0744	
0745	
0746	
07468	
0747	
0748	
0749	
075	
0761	
076	
0763	
0765	
0766	
0767	
0768	
0770	
0771	
0772	
0773	
0774	
077	
0776	
0778	
0779	
078	
0790	
0791	
079	
0794	
0795	
0796	
0797	
0798	
0799	
082	
0820	
0823	
0824	
0826	
0827	
0829	
083	
0833	
0834	
0835	
0836	
0837	
0838	
08387	
08388	
08396	
0845	
0846	
0847	
08477	
0848	
084	
08512	
08514	
0852	
0853	
0854	
0855	
0856	
0857	
0858	
0859	
086	
0863	
0865	
0866	
0867	
0868	
0869	
0875	
0877	
087	
0879	
0880	
0883	
0884	
0885	
088	
0887	
0889	
0892	
0893	
0894	
0895	
0896	
0897	
0898	
089	
092	
0920	
093	
0930	
0940	
0942	
0943	
0944	
0946	
0947	
0948	
0949	
09496	
0950	
0952	
0954	
0955	
0956	
0957	
095	
0959	
096	
0964	
0965	
0966	
0967	
0968	
0969	
0972	
0973	
0974	
097	
0977	
0978	
0979	
098	
0980	
09802	
0982	
0983	
0984	
0985	
0986	
0987	
09912	
09913	
099	
0993	
0994	
0995	
0996	
09969	
0997	

コード内容

ちょっと眠いので一言で。
市外局番リストの中で最大文字数の数字分を対象に、マスタと成形前の電話番号をそれぞれその数字分で比較して一通り終わったら、－1してまた比較・・・を繰り返してるよ！

使用方法

1.CodeMaster シートに上記市外局番をA列に貼り付け
2.PhoneNumbers シートのA列に電話番号入力
3.マクロ実行でB列にハイフン付与

Excel VBAはエンジニア業界だと見下されている言語の一つだけど、ある程度のことなら何でもできる便利な言語だよ！
有料に頼らずとも、皆様のお力になれたらいいな。
そんなわけでまた！