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

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

元データでは
0123450000000

整形後
0123-450000000

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

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

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

No.7 ベストアンサー 回答者： grumpy_the_dwarf 回答日時： 2009/12/09 00:19

元データと市外局番一覧がきちんと文字列として存在することを前

提に、元データの先頭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さんのほど配列が巨大化しないのも特徴。

この回答へのお礼

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

お礼日時：2009/12/09 10:25

No.6 回答者： MackyNo1 回答日時： 2009/12/09 00:04

「０４２」と「０４２２」など最初が同じ市外局番の場合は短いものから長いものへの順に並べてある局番リストが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で確定してください。

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

この回答へのお礼

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

お礼日時：2009/12/09 10:26

No.5 回答者： Wendy02 回答日時： 2009/12/08 21:01

こんばんは。

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

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

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

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

この回答へのお礼

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

お礼日時：2009/12/09 10:26

No.4 回答者： imogasi 回答日時： 2009/12/08 21:00

関数でできるという前提で質問しているが、エクセルの関数の経験不足で言っているとおもう。

難しいことだ。
ーー
多分ＶＢＡで無いと出来ないと思う。
事実上の市外局番桁数の制約を当てにしないでは、最長一致を式にするのは無理だろう。
ーー
まず市外局番の参照テーブルが必要
例
03
04
042
・・
しかしセルにある番号数字から、何桁比較したら良いかわからない（まあこれを求めるのが本質問なのだが）
繰り返して該当を探し、チェックして見つけないとならない。
ーー
それに０４、０４２などあるところから０４でチェックするとダメで
一番長い０４２（この少数例の場合）からチェックしないとならない。
ーー
文章内で、指定文字列に一致する場所を探す検索や置換のアルゴリズムがあるが、難しいので、初歩的にやってみると
検索表（例えば）
Ｇ列　　　Ｈ列
052345
04274
0423
032
042
052
Ｈ列はＧ列の文字列長でこの長さ優先キーとしてソートしている。
ーーー
データ　例
下記結果のＡ列
ーー
ＶＢＡコード　標準モジュールに
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
ーーー
結果　下記のＢ列
Ａ列　　　　　　Ｂ列
032345123403-23451234
043458972　　04-3458972
04272345670427-234567
052342345　　　　05234-2345
注意点は、04のテーブルを作る場合、ありえる04の付く市外局番04XXXの全てのテーブルを用意しないと、データによっては正しいい値に、ーが入らない恐れがある。
ーー
なお電話番号体系は、回線の接続宛先を探すには都合が良い体系のようだ。
電話を架けるときは、そのまま先頭からダイヤルすれば良いのだし。

この回答へのお礼

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

お礼日時：2009/12/09 10:27

No.3 回答者： hallo-2007 回答日時： 2009/12/08 20:39

仮に

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 回答日時： 2009/12/08 19:39

元データが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に出ます。

この回答へのお礼

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

お礼日時：2009/12/09 10:27

No.1 回答者： Tasuke22 回答日時： 2009/12/08 18:45

規則性がヘンなので自分でマクロを作るのは確実と

思います。
電電公社の発送は・・・面白い。

この回答へのお礼

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

お礼日時：2009/12/09 10:28