市外局番と市内局番以降 に２分割

質問

Excelにて、電話番号を市外局番と市内局番以降　に分離・整理したいです。

元データでは
0123450000000

整形後
0123-450000000

参照データとして市外局番一覧は入手済みです。
0123
01234　など

・間にハイフンが入ればいいです。（セルで分割でも　可）
・市内局番以降は一連で構わないです。
・それに市町村名なども不要です。

単純に分けたいだけです。対応できるExcelの関数を教えて下さい。

回答No.7

grumpy_the_dwarf · ベストアンサー

元データと市外局番一覧がきちんと文字列として存在することを前
提に、元データの先頭2桁から5桁の範囲で一覧表と最長一致で検索
出来れば良さげということですね。もしかしたら6桁かな。

とりあえず5桁ってことで一覧表がソートされてる必要のない数式
はこれ。Wendy02さんのreplace関数を流用させてもらってます。
=replace(A1,max(not(isna(match(left(A1,{2,3,4,5}),F:F,0)))*{2,3,4,5})+1,0,"-")
もちろん市外局番の最長が6桁なら、{2,3,4,5}が{2,3,4,5,6}にな
るだけ。Wendy02さんのほど配列が巨大化しないのも特徴。

お礼

ありがとうございました、試してみます。画像まで！！

回答No.6

MackyNo1

「０４２」と「０４２２」など最初が同じ市外局番の場合は短いものから長いものへの順に並べてある局番リストがF1:F400セルに入力されている場合、以下の数式で文字列書式に入力されたA２セルの局番を「-」で分離することができます（重複すときは局番の長いほうを検索）。

=0&MAX((COUNTIF(A2,$F$1:$F$400&"*")=1)*($F$1:$F$400))&"-"&MID(A2,LEN(MAX((COUNTIF(A2,$F$1:$F$400&"*")=1)*($F$1:$F$400)))+2,10)

配列数式ですので入力後Ctrl+Shift+Enterで確定してください。

ちなみに、上記の数式は局番の先頭部分の数字を検索する形になっていますので、市内局番以下の部分に同じ数字の並びがある場合でも、それらはヒットしない数式になっています。

お礼

ありがとうございました、試してみます。

回答No.5

Wendy02

こんばんは。

問題は、参照データ数に依存してきます。

A1:
0123450000000

B1:
=REPLACE(A1,MAX(INDEX(NOT(ISERROR(FIND($F$1:$F$400,A1)))*ROW($F$1:$F$400),,))+1,0,"-")

F1:～（参照データ）
0123
01234

エラー処理はしていません。
失敗している場合は、先頭に、「-」が出てきます。
ソートされていて、データの最後にヒットしたものを、選び出します。

参照データ数が多い場合は、マクロにすべきかもしれません。
基本的には、上記の数式を移植すればよいような気がしています。

お礼

ありがとうございました、試してみます。

回答No.4

imogasi

関数でできるという前提で質問しているが、エクセルの関数の経験不足で言っているとおもう。難しいことだ。
ーー
多分ＶＢＡで無いと出来ないと思う。
事実上の市外局番桁数の制約を当てにしないでは、最長一致を式にするのは無理だろう。
ーー
まず市外局番の参照テーブルが必要
例
03
04
042
・・
しかしセルにある番号数字から、何桁比較したら良いかわからない（まあこれを求めるのが本質問なのだが）
繰り返して該当を探し、チェックして見つけないとならない。
ーー
それに０４、０４２などあるところから０４でチェックするとダメで
一番長い０４２（この少数例の場合）からチェックしないとならない。
ーー
文章内で、指定文字列に一致する場所を探す検索や置換のアルゴリズムがあるが、難しいので、初歩的にやってみると
検索表（例えば）
Ｇ列　　　Ｈ列
05234 5
0427 4
042 3
03 2
04 2
05 2
Ｈ列はＧ列の文字列長でこの長さ優先キーとしてソートしている。
ーーー
データ　例
下記結果のＡ列
ーー
ＶＢＡコード　標準モジュールに
Sub test01()
d = Range("A65536").End(xlUp).Row
e = Range("G65536").End(xlUp).Row
For i = 1 To d
For j = 1 To e
If Left(Cells(i, "A"), Len(Cells(j, "G"))) = Cells(j, "G") Then
Cells(i, "B") = Cells(j, "G") & "-" & Right(Cells(i, "A"), Len(Cells(i, "A")) - Len(Cells(j, "G")))
Exit For
End If
Next j
Next i
End Sub
ーーー
結果　下記のＢ列
Ａ列　　　　　　Ｂ列
0323451234 03-23451234
043458972　　 04-3458972
0427234567 0427-234567
052342345 　　　　05234-2345
注意点は、04のテーブルを作る場合、ありえる04の付く市外局番04XXXの全てのテーブルを用意しないと、データによっては正しいい値に、ーが入らない恐れがある。
ーー
なお電話番号体系は、回線の接続宛先を探すには都合が良い体系のようだ。
電話を架けるときは、そのまま先頭からダイヤルすれば良いのだし。

お礼

ありがとうございました、試してみます。

回答No.3

hallo-2007

仮に
A列に電話番号　　
D列に　市外局番一覧　があって昇順に並んでいるとします。

B1セルに　=INDEX(D:D,MAX(IF(COUNTIF(D:D,LEFT(A4,ROW(A$2:A$6)))=0,0,MATCH(LEFT(A4,ROW(A$2:A$6)),D:D))))
Ctrl+Shift+Enter　で配列関数にします。関数が{}でくくられます。
B1セルを下へコピィすると合致した市外局番が表示されます。
C列は=SUBSTITUTE(A2,B2,"")
で下へコピィします。

回答No.2

f272

元データがK3にあって，市外局番一覧が$D$3:$D$42693にあるとしたとき
L3=IF(ISERROR(MATCH(LEFT(K3,5),$D$3:$D$42693,0)),
IF(ISERROR(MATCH(LEFT(K3,4),$D$3:$D$42693,0)),
IF(ISERROR(MATCH(LEFT(K3,3),$D$3:$D$42693,0)),
IF(ISERROR(MATCH(LEFT(K3,2),$D$3:$D$42693,0)),0,
INDEX($D$3:$D$42693,MATCH(LEFT(K3,2),$D$3:$D$42693,0))),
INDEX($D$3:$D$42693,MATCH(LEFT(K3,3),$D$3:$D$42693,0))),
INDEX($D$3:$D$42693,MATCH(LEFT(K3,4),$D$3:$D$42693,0))),
INDEX($D$3:$D$42693,MATCH(LEFT(K3,5),$D$3:$D$42693,0)))
でL3に市外局番が出ます。
そしてそれ以降は
M3=SUBSTITUTE(K3,L3,"")
でM3に出ます。

お礼

ありがとうございました、試してみます。

回答No.1

Tasuke22

規則性がヘンなので自分でマクロを作るのは確実と
思います。
電電公社の発送は・・・面白い。

お礼

ありがとうございました。そうですね。