- ベストアンサー
市外局番と市内局番以降 に2分割
- みんなの回答 (7)
- 専門家の回答
質問者が選んだベストアンサー
元データと市外局番一覧がきちんと文字列として存在することを前 提に、元データの先頭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さんのほど配列が巨大化しないのも特徴。
その他の回答 (6)
- MackyNo1
- ベストアンサー率53% (1521/2850)
「042」と「0422」など最初が同じ市外局番の場合は短いものから長いものへの順に並べてある局番リストがF1:F400セルに入力されている場合、以下の数式で文字列書式に入力されたA2セルの局番を「-」で分離することができます(重複すときは局番の長いほうを検索)。 =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で確定してください。 ちなみに、上記の数式は局番の先頭部分の数字を検索する形になっていますので、市内局番以下の部分に同じ数字の並びがある場合でも、それらはヒットしない数式になっています。
お礼
ありがとうございました、試してみます。
- Wendy02
- ベストアンサー率57% (3570/6232)
こんばんは。 問題は、参照データ数に依存してきます。 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 エラー処理はしていません。 失敗している場合は、先頭に、「-」が出てきます。 ソートされていて、データの最後にヒットしたものを、選び出します。 参照データ数が多い場合は、マクロにすべきかもしれません。 基本的には、上記の数式を移植すればよいような気がしています。
お礼
ありがとうございました、試してみます。
- imogasi
- ベストアンサー率27% (4737/17068)
関数でできるという前提で質問しているが、エクセルの関数の経験不足で言っているとおもう。難しいことだ。 ーー 多分VBAで無いと出来ないと思う。 事実上の市外局番桁数の制約を当てにしないでは、最長一致を式にするのは無理だろう。 ーー まず市外局番の参照テーブルが必要 例 03 04 042 ・・ しかしセルにある番号数字から、何桁比較したら良いかわからない(まあこれを求めるのが本質問なのだが) 繰り返して該当を探し、チェックして見つけないとならない。 ーー それに04、042などあるところから04でチェックするとダメで 一番長い042(この少数例の場合)からチェックしないとならない。 ーー 文章内で、指定文字列に一致する場所を探す検索や置換のアルゴリズムがあるが、難しいので、初歩的にやってみると 検索表(例えば) G列 H列 05234 5 0427 4 042 3 03 2 04 2 05 2 H列はG列の文字列長でこの長さ優先キーとしてソートしている。 ーーー データ 例 下記結果のA列 ーー VBAコード 標準モジュールに 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 ーーー 結果 下記のB列 A列 B列 0323451234 03-23451234 043458972 04-3458972 0427234567 0427-234567 052342345 05234-2345 注意点は、04のテーブルを作る場合、ありえる04の付く市外局番04XXXの全てのテーブルを用意しないと、データによっては正しいい値に、ーが入らない恐れがある。 ーー なお電話番号体系は、回線の接続宛先を探すには都合が良い体系のようだ。 電話を架けるときは、そのまま先頭からダイヤルすれば良いのだし。
お礼
ありがとうございました、試してみます。
- hallo-2007
- ベストアンサー率41% (888/2115)
仮に 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,"") で下へコピィします。
- f272
- ベストアンサー率46% (7998/17100)
元データが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に出ます。
お礼
ありがとうございました、試してみます。
- Tasuke22
- ベストアンサー率33% (1799/5383)
規則性がヘンなので自分でマクロを作るのは確実と 思います。 電電公社の発送は・・・面白い。
お礼
ありがとうございました。そうですね。
関連するQ&A
- 全国の市外局番の一覧が載っているサイトありませんか
仕事で住所、電話などのデータをある形式に揃える必要があるのですが、ハイフンなしの電話番号をハイフン付に変換するのが大変です。例えば03****2222は03-****-2222となり、045***4444は045-***-4444となります。市外局番が2桁、3桁、4桁(たまに5桁の所も)と地区によってバラバラなので、計算式の利用にも限度があり、困っています。そこで質問ですが、 1.全国の市町村名と市外局番が一覧になっているサイトはありませんか。 2.エクセルで、ハイフンなしの電話番号に市外局番の箇所でハイフンを付けるには、どのようにすればいちばん早いでしょうか。ちなみに今はMID関数を使って、東京なら03だけを取り出して・・・というようにやっています。
- ベストアンサー
- その他(インターネット接続・通信)
- 市外局番市内局番の一覧リストを探しています
仕事の関係で電話番号から該当の市区町村を割り出す作業を しているのですがあまりにもデータ件数が膨大なために 1つずつ検索している余裕がありません。 電話番号はExcelで一覧リストになっているので 市外局番+市内局番の6桁と該当する市区町村を 対比させたリストがあればVLOOKUP関数とLEFT関数で 一括変換できると思い市外局番市内局番一覧リストを探しています。 自分でもネットでいろいろと探してみましたが現在は固定電話事業者も 複数あるためか総務省HPで割当事業者までは分かるものの 個々の市内局番がどの市区町村になるかというリストは存在しませんでした。 このようなリストの存在をご存知の方がいらっしゃいましたら サイト名もしくはサイトアドレスの情報を著作権など法的問題がないのでしたら ご提供いただけたら大変助かります。 なお、個別事業者番号は考慮しないもので問題ありません。 宜しくお願い申し上げます。
- 締切済み
- その他(インターネット・Webサービス)
- 郵便番号と市外局番のデータ
住所録を作りたいのですが、郵便番号・市外局番・県市町村名の一覧データがダウンロード出来るサイトってないでしょうか? 郵便局(http://www.post.yusei.go.jp/)のデータは市外局番がなかったので。 ヨロシクお願いします。
- ベストアンサー
- その他(データベース)
- エクセルで電話番号から市内局番だけを抜き出したいのですが
エクセルで、「○○-△△-××××」というようにハイフンで区切られた電話番号が入力されたセルに対し 市内局番だけ(または最後の番号だけ)を抜き出したいのですが、そういうことは可能でしょうか? よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 市外、市内局番から県コードが特定出来るデータ
初めて質問します。 よろしくお願いします。 ●やりたいことは、 「電話番号の市外局番、市内局番を入力して 県コードを特定する」です。 ●欲しいデータは、 「市外局番、市内局番、県コード、住所」のデータです。 ※住所は無くても、可 総務省HPから、 「電気通信番号指定状況-固定電話の電話番号」 を、見つけることは出来ましたが、県の特定までは 出来ません。 ※メッセージエリア(MA)から、県を探し出す事も、可能ですが・・・。 システム上、電話番号から県を特定する必要があり、 同じようなご経験の方で、どのように解決されたか、 あわせて教えていただけると助かります。
- 締切済み
- その他([技術者向] コンピューター)
- 市外局番のハイフンの位置について
こんにちは。市外局番が4桁から3桁になる地域が多くありますが、市外局番のハイフンの位置の最新情報というのは、あるのでしょうか?一覧みたいなものがあればベストなのですが。ご存知の方、いらっしゃいますか?
- ベストアンサー
- その他(生活・暮らし)
- 市外局番の一覧を見たいのですが
市外局番の一覧を見たいのですが http://www.soumu.go.jp/main_sosiki/joh_tsusin/top/tel_number/shigai_list.html を見ると、ワードとPDFしかないのですが エクセルバージョンはないのでしょうか?
- ベストアンサー
- 家庭用電話機・FAX
- 電話番号の適正位置に「ー」を表示したい
こんにちは。 システムの入替に伴い、顧客のデータを移行しようとしています。 その中で電話番号の項目があるのですが、現システムでは ハイフンを表示しない形式で登録されています。 新システムのDBではハイフン付で登録しなければならないのですが、 市外局番・市内局番の桁数が地域によって異なるため、 ハイフンで区切る適正位置がわからず困っています。 ネットから市外局番一覧を入手したのですが、ExcelのVlookupで 突合させるとニアリーイコールのものを引っ張ってしまい、 上手にセットすることができません。 どなたか良い方法や変換ソフトなどをご存知の方がいらっしゃったら教えてください。よろしくお願いします。
- ベストアンサー
- その他(ソフトウェア)
- エクセル2000 電話番号の市外局番を入れたFAX番号を表示したい
市外局番からの電話番号と隣の列に市外局番を抜いた(同じ番号なので)FAX番号のデータが入っております。 A B 電話番号 FAX番号 1 XXX-XXXX-XXXX XXXX-XXXX 2 XXX-XXXX-XXXX XXXX-XXXX と言った具合になります。 これを、C列に市外局番を加えたFAX番号を表示するようにしたいので、C1に=LEFT(A1,4)&B1といれて、そのままオートフィルをかけたのですが、地域によって市外局番が2~5桁ありますので、市外局番が3桁以外の部分は目で見てLEFT関数の桁数を打ち変えております。 これを関数か何かで一括で出来る方法はないものかと思い質問させていただきました。 A B C 電話番号 FAX番号 FAX番号2 1 XXX-XXXX-XXXX XXXX-XXXX XXX-XXXX-XXXX 2 XXX-XXXX-XXXX XXXX-XXXX XXX-XXXX-XXXX 最終的に上のような形にしたいのです。 この一覧表は変わることがありますし、データが追加されるときはデータの一番下にあるわけではないので、変わるたびに一から入力しなおさなければなりません。 -の前の数字(市外局番)を数えられればいいと思うのですが、それをカウントできるやり方はあるのでしょうか? よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
お礼
ありがとうございました、試してみます。画像まで!!