電話番号の入力方式が違うデータを統一させる処理をしたい

３０万行あるエクセルのデータで、電話番号列に下記のような入力パターンがあります。

1、[phone]　　　　→市外局番を()で括ってある
2、012-1231234　　　　→ハイフンが市外局番の後だけ
3、０１２－１２３－１２３４　　　　→全角になっている
4、012-123-1234(内線12)　　　　→後ろに内線番号がある
5、012-123-1234(12)　　　　→後ろに内線番号がある
6、012-123-1234　090-1234-1234　　　　→番号が2つ書いてある
7、'0121231234　　　　→先頭に'がある　(文字列にする為)
8、121231234　　　　→先頭の0が消えている　(数値になっている為)
9、1231234　　　　→市外局番の入力が無い
10、「ﾅｼ」「無し」「なし」と書かれている。
11、9012341234　　　　→先頭の0が消えた携帯番号

これを下記のように綺麗にしたいのですが
関数の組み合わせで処理できますか？

1、012-123-1234
2、012-123-1234
3、012-123-1234
4、012-123-1234
5、012-123-1234
6、012-123-1234
7、012-123-1234
8、012-123-1234
9、空欄にする
10、空欄にする
11、090-1234-1234


1～8番まで同じ電話番号なわけですが、入力方式が違う為、
別のデータとして扱われてしまうのを避けたいのです。

何卒、宜しくお願い致します。

No.1 回答者： CoalTar 回答日時： 2015/03/20 18:54

途中経過でごめんなさい

A2セルから下に入力されているとして
第一段階 B2セルに
=IF(LEN(A2)<8,"",(SUBSTITUTE(REPLACE(SUBSTITUTE(SUBSTITUTE(LEFT(ASC(A2),6),"(",""),")"," ")&RIGHT(ASC(A2),LEN(A2)-6),8,," "),"-"," ")))

第二段階 C2セルに
=LEFT(B2,FIND("(",SUBSTITUTE(TRIM(B2)," ","(",3)&"(")-1)

この回答へのお礼

ご回答ありがとうございます。

24日に出社するので、ご提示の方法を試してみます。

お礼日時：2015/03/21 22:53

No.2 ベストアンサー 回答者： tom04 回答日時： 2015/03/20 19:33

こんばんは！

VBAでの一例です。

携帯電話番号が含まれている場合も考慮してみました。
A2セル以降にデータがあり、B2セル以降に表示するとします。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, str As String, buf As String
Range("B:B").ClearContents
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row '//2行目～A列最終行まで
For k = 1 To Len(Cells(i, "A"))
str = Mid(StrConv(Cells(i, "A"), vbNarrow), k, 1)
If str Like "[0-9]" Then
buf = buf & str
End If
If Len(buf) = 3 Then
If buf = "070" Or buf = "080" Or buf = "090" Then
If Len(buf) = 11 Then Exit For
ElseIf Len(buf) = 10 Then Exit For
End If
End If
Next k
If Len(buf) >= 9 Then
If Left(buf, 1) <> "0" Then
buf = "0" & buf
End If
If Len(buf) = 10 Then
Cells(i, "B") = Left(buf, 3) & "-" & Mid(buf, 4, 3) & "-" & Right(buf, 4)
Else
Cells(i, "B") = Left(buf, 3) & "-" & Mid(buf, 4, 4) & "-" & Right(buf, 4)
End If
End If
buf = ""
Next i
MsgBox "処理完了"
End Sub 'この行まで

※　データが30万行あるというコトですので、
かなり時間を要すると思います。
（途中で「応答なし」になると思いますが、じっくり待ってみてください。m(_ _)m

この回答へのお礼

ご回答ありがとうございます。

マクロ凄いですね。
自分もマクロが使えるようになりたいです。

24日に出社するので、ご提示の方法を試してみます。

追ってご報告致します。

お礼日時：2015/03/21 22:57

No.3 回答者： mike_g 回答日時： 2015/03/21 07:38

ハイフンが固定電番を３桁-３桁-４桁に、携帯電番を３桁-４桁-４桁に区切っているのを、貴方は「綺麗」と思っているようだけど、0120-123-123、0120-12-1234、03-1234-1234と表記されたりするのが一般的だったりします。

いっそのことハイフンなしの電番に統一するてのは如何ですか？

この回答へのお礼

ご回答ありがとうございます。

そうですね。
３桁-３桁-４桁の例しか書いていませんでしたが、
データには03-と区切られているものもあります。

ですので、厳密にはもっとパターンがあります。

ただし、ハイフンを入れたいのは、電話機でダイヤルする時に、
ハイフンがある方が読み易く、間違い電話になる確率が低くなるからです。

つまり、読みやすくするのが目的なので、
03-1234-1234　が　031-234-1234になってしまったとしても、
業務上はとくに問題ありません。

宜しくお願い致します。

お礼日時：2015/03/21 23:08

No.4 回答者： CoalTar 回答日時： 2015/03/21 16:51

No.1です

No.3のmike_gさんのおっしゃるように、市外局番の区切りは正確には無理そう
携帯は11桁だし、左から3桁目が0なので判断がつくけど。

第１段階 B2セルに
=IF(LEN(A2)<8,"",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC(A2),"("," "),"'"," "),")"," "),"-"," ")))
かっことハイフンをを半角スペースにし、文字数の短いものは空白とした

第2段階 C2セルに
=IF(OR(B2="",LEFT(B2,1)="0"),"",0)&LEFT(B2,FIND(")",SUBSTITUTE(B2&")"," ",")",3))-1)
0を付加、後ろを削除

第３段階 D2セルに
=IF(C2="","",SUBSTITUTE(TRIM(REPLACE(REPLACE(C2,LEN(C2)-3,0," "),4,0," "))," ","-"))
後ろから4と5番目の間、前から3番目と4番目の間にハイフンを入れた

件数があるからイマイチ数式向きではないかも
各段階で値に置き換えれば少しはマシかな