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さんのほど配列が巨大化しないのも特徴。
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で質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
歩いた自慢大会
「めちゃくちゃ歩いたエピソード」を教えてください。 長時間でも長距離でも結構です。
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
エクセルで電話番号から市内局番だけを抜き出したいのですが
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
タイムスタンプとテキストから...
-
エクセルの文字が途中から消える
-
エクセルの質問です。 F列からL...
-
ワークシートに出現したこの画...
-
Excelの警告について
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
Excelでの文字色
-
マクロの処理が遅くなった
-
シートの情報を別のシートへま...
-
OFFSET関数を使用したいのです...
-
エクセルの数式バーのフォント...
-
エクセルデーターから必要な項...
-
Excelについて教えてください。...
-
SUBTOTALは、参照された数字で...
-
エクセルの「条件付き書式」を...
-
Excelの関数について このよう...
-
エクセル。金額から「円」を除...
-
Excelの数字の前に入っている空...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報