![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
No.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さんのほど配列が巨大化しないのも特徴。
![「市外局番と市内局番以降 に2分割」の回答画像7](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/f/55433_5497e79342249/M.jpg)
No.6
- 回答日時:
「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で確定してください。
ちなみに、上記の数式は局番の先頭部分の数字を検索する形になっていますので、市内局番以下の部分に同じ数字の並びがある場合でも、それらはヒットしない数式になっています。
No.5
- 回答日時:
こんばんは。
問題は、参照データ数に依存してきます。
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
- 回答日時:
関数でできるという前提で質問しているが、エクセルの関数の経験不足で言っているとおもう。
難しいことだ。ーー
多分VBAで無いと出来ないと思う。
事実上の市外局番桁数の制約を当てにしないでは、最長一致を式にするのは無理だろう。
ーー
まず市外局番の参照テーブルが必要
例
03
04
042
・・
しかしセルにある番号数字から、何桁比較したら良いかわからない(まあこれを求めるのが本質問なのだが)
繰り返して該当を探し、チェックして見つけないとならない。
ーー
それに04、042などあるところから04でチェックするとダメで
一番長い042(この少数例の場合)からチェックしないとならない。
ーー
文章内で、指定文字列に一致する場所を探す検索や置換のアルゴリズムがあるが、難しいので、初歩的にやってみると
検索表(例えば)
G列 H列
052345
04274
0423
032
042
052
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列
032345123403-23451234
043458972 04-3458972
04272345670427-234567
052342345 05234-2345
注意点は、04のテーブルを作る場合、ありえる04の付く市外局番04XXXの全てのテーブルを用意しないと、データによっては正しいい値に、ーが入らない恐れがある。
ーー
なお電話番号体系は、回線の接続宛先を探すには都合が良い体系のようだ。
電話を架けるときは、そのまま先頭からダイヤルすれば良いのだし。
No.3
- 回答日時:
仮に
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
- 回答日時:
元データが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に出ます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(生活家電) 左詰めなの右なの 5 2022/06/19 09:31
- 固定電話・IP電話・FAX 電話番号について 市外局番以外の数字から地域をある程度特定できますか? 5 2023/08/15 23:26
- Excel(エクセル) 【Excel】住所に郵便番号を付記する方法 3 2022/05/07 17:15
- 郵便・宅配 郵便局の電話番号ってなぜ1つの局ってだけでナビダイヤルかつ自動音声なのですか? 本社へのコールセンタ 2 2023/02/05 00:06
- FTTH・光回線 大阪市内のマンション引越しでのブロードバンド契約 2 2023/03/11 07:27
- 固定電話・IP電話・FAX 03から始まる電話番号って固定電話ですか?かけ放題無料? 3 2023/02/23 15:09
- iPhone(アイフォーン) web上の電話番号リンクの桁区切りが、iPhoneだと特定電話番号でおかしくなるのを解決したい 1 2023/03/28 14:27
- その他(暮らし・生活・行事) 連絡したい相手の住所と電話番号を紛失して困っています。 4 2022/05/06 21:55
- Excel(エクセル) EXCELでの複雑な複数条件について 4 2022/05/09 16:19
- その他(国内) 知らない電話番号から電話が来ました 7 2023/08/10 14:10
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで 自動的に◯や数字を...
-
【マクロ】2回実行したら、エ...
-
エクセルのツールバーから数値...
-
特定の文字列を含む、住所を抽...
-
Excel 2019 [オプション]の[リボンのユ...
-
祝日と土曜、日曜の合計をカウ...
-
EXCELの散布図で日付が1900年に...
-
マイクロソフトのPADを使ってい...
-
絶対参照
-
エクセルでCtrl+Tでテーブルの...
-
【マクロ】名前を保存する際に...
-
エクセルのクイックアクセスツ...
-
【マクロ】VLOOKUPにて参照元に...
-
Excel分数の表示について
-
【EXCEL】画像の黄色部分の抽出...
-
DATE関数で現在の年齢を出した...
-
Excelについて
-
マクロエクセルのブロック解除
-
Excelピボットテーブルの1行目
-
REGEXREPLACE関数について、
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報