![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?a65a0e2)
30万行あるエクセルのデータで、電話番号列に下記のような入力パターンがあります。
1、(012)1231234 →市外局番を()で括ってある
2、012-1231234 →ハイフンが市外局番の後だけ
3、012-123-1234 →全角になっている
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.2ベストアンサー
- 回答日時:
こんばんは!
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日に出社するので、ご提示の方法を試してみます。
追ってご報告致します。
No.4
- 回答日時:
No.1です
No.3のmike_gさんのおっしゃるように、市外局番の区切りは正確には無理そう
携帯は11桁だし、左から3桁目が0なので判断がつくけど。
第1段階 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を付加、後ろを削除
第3段階 D2セルに
=IF(C2="","",SUBSTITUTE(TRIM(REPLACE(REPLACE(C2,LEN(C2)-3,0," "),4,0," "))," ","-"))
後ろから4と5番目の間、前から3番目と4番目の間にハイフンを入れた
件数があるからイマイチ数式向きではないかも
各段階で値に置き換えれば少しはマシかな
No.3
- 回答日時:
ハイフンが固定電番を3桁-3桁-4桁に、携帯電番を3桁-4桁-4桁に区切っているのを、貴方は「綺麗」と思っているようだけど、0120-123-123、0120-12-1234、03-1234-1234と表記されたりするのが一般的だったりします。
いっそのことハイフンなしの電番に統一するてのは如何ですか?
ご回答ありがとうございます。
そうですね。
3桁-3桁-4桁の例しか書いていませんでしたが、
データには03-と区切られているものもあります。
ですので、厳密にはもっとパターンがあります。
ただし、ハイフンを入れたいのは、電話機でダイヤルする時に、
ハイフンがある方が読み易く、間違い電話になる確率が低くなるからです。
つまり、読みやすくするのが目的なので、
03-1234-1234 が 031-234-1234になってしまったとしても、
業務上はとくに問題ありません。
宜しくお願い致します。
No.1
- 回答日時:
途中経過でごめんなさい
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)
![「電話番号の入力方式が違うデータを統一させ」の回答画像1](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/3/655143_550bee5eab8b4/M.jpg)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【Excel】住所に郵便番号を付記する方法 3 2022/05/07 17:15
- Visual Basic(VBA) セルに抜けた番号の代わりに空白を挿入する 4 2023/04/10 20:29
- Excel(エクセル) 製品番号での整列と、検索に関して 3 2023/06/28 19:20
- その他(Microsoft Office) EXCELの1行を1枚の用紙にそれぞれ印刷したい。 3 2022/10/10 11:35
- Java Java 南京錠 2 2023/02/04 11:46
- Excel(エクセル) 【条件付き書式】countifsで複数条件を満たしたセルを赤くする方法 2 2023/02/09 23:53
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- Excel(エクセル) ある数値に対して、値を返す数式についてです 2 2022/09/13 22:06
- PHP コメント機能に返信欄を矢印で追加したい 1 2022/05/09 21:17
このQ&Aを見た人はこんなQ&Aも見ています
-
これまでで一番「情けなかったとき」はいつですか?
これまでの人生で一番「情けない」と感じていたときはいつですか? そこからどう変化していきましたか?
-
何回やってもうまくいかないことは?
みなさんには、何回やってもうまくいかないことはありますか?
-
モテ期を経験した方いらっしゃいますか?
一生に一度はモテ期があるといいますが、みなさんどうですか? いまがそう! という方も、「思い返せばこの頃だったなぁ」という方も、よかったら教えて下さい。
-
AIツールの活用方法を教えて
みなさんは普段どのような場面でAIツール(ChatGPTなど)を活用していますか?
-
「これいらなくない?」という慣習、教えてください
現代になって省略されてきたとはいえ、必要性のない慣習や風習、ありませんか?
-
エクセル 電話番号に市外局番をつけたい
Excel(エクセル)
-
電話番号の()を一括でハイフンに出来ませんか?
Excel(エクセル)
-
Excelで、国際委電話番号表示を市外局番からの番号へ変換方法
Excel(エクセル)
-
-
4
エクセルで電話番号から市内局番だけを抜き出したいのですが
Excel(エクセル)
-
5
参照先セルに値が入っていない時に、「0」や「1900/01/00」などが入らないようにしたいのですが
Excel(エクセル)
-
6
ハイフン付きの電話番号の先頭 0 を消し、先頭に+81を付ける方法
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・「これはヤバかったな」という遅刻エピソード
- ・初めて自分の家と他人の家が違う、と意識した時
- ・いちばん失敗した人決定戦
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【Excel関数】UNIQUE関数で"0"...
-
結合されたセルをプルダウンの...
-
特定の文字がある行以外を削除...
-
Excel グラフのプロットからデ...
-
エクセルのセルに指定画像(.jpg...
-
エクセル マクロ オートフィ...
-
エクセル マクロで数値が変っ...
-
エクセルで特定の文字列が入っ...
-
電話番号の入力方式が違うデー...
-
エクセルマクロで偶数行(又は...
-
EXCELマクロを使い、空白行では...
-
excelのデータで色つき行の抽出...
-
連続データが入った行の一番右...
-
[EXCEL]ボタン押す→時刻が表に...
-
エクセルVBA 最終行を選んで並...
-
エクセル 時間の表示形式AM/PM...
-
A1に入力された文字列と同じ文...
-
アクティブになっている行をマ...
-
エクセル 上下で列幅を変えるには
-
Excelでカタカナ・ひらがな・英...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【Excel関数】UNIQUE関数で"0"...
-
エクセルで特定の文字列が入っ...
-
特定の文字がある行以外を削除...
-
[EXCEL]ボタン押す→時刻が表に...
-
Excel グラフのプロットからデ...
-
エクセル マクロ オートフィ...
-
エクセル マクロで数値が変っ...
-
結合されたセルをプルダウンの...
-
Excelのフィルター後の一番上の...
-
excel 小さすぎて見えないセル...
-
エクセル 上下で列幅を変えるには
-
エクセルマクロで偶数行(又は...
-
Excel ウインドウ枠の固定をす...
-
EXCELで最後の行を固定
-
VBAで色の付いているセルの行削除
-
連続データが入った行の一番右...
-
エクセル 時間の表示形式AM/PM...
-
エクセルのセルに指定画像(.jpg...
-
エクセルVBA 最終行を選んで並...
-
アクティブになっている行をマ...
おすすめ情報