文章情報の中から電話番号とFAX番号を抜き出す関数を教えて下さい

A1を電話番号用の空欄セル、B1をFAX番号用の空欄セル、C1には会社の郵便番号や住所、代表者の名前、担当者の名前、そのほか文章でいろいろな情報や数字が入っている中に「電話番号03-1234-5678」「FAX[phone]」があるとします。
その文中の電話番号とFAX番号のみをA1とB1のセルにそれぞれ振り分ける関数があればおしえていただけないでしょうか。

文章中の電話番号やFAX番号の文字位置は不確定です。ただし、それぞれの番号の前には必ず「電話番号」「FAX」と表示された後数字が続きます。

質問者からの補足コメント

• 

  貴重なご意見をありがとうございます。
  確かに050や0120、市外局番にゼロがない記載漏れなどがあり、すべてが１０桁であるとは言えない場合があったことに気づきました。
  そこで頂いたとおりにVBEにコピペ（再度Alt+F11で見てもModule1となっておりコピーしたものも表示されている）し終了。
  A1、B1それぞれのセルにも関数を入力しましたが、反応なしです。
  
  原因がおわかりになれば教えていただけますでしょうか。

  No.2の回答に寄せられた補足コメントです。 補足日時：2020/08/05 17:51

• 

  ありがとうございます。
  10桁の電話番号に対応できました。
  ご教示いただきました関数の他に、11桁の番号や記載漏れなどで9桁になってしまったものがあることを再発見しました。
  9桁、10桁、11桁にも対応できるような関数をご存知でしょうか？

  No.1の回答に寄せられた補足コメントです。 補足日時：2020/08/05 21:16

• 

  ありがとうございます。エクセル2019での対応となります。
  いただきました関数を入力しましたところ、電話番号では#N/A,FAXでは#VALUEが返されました。
  
  例えば文字列には「株式会社○○"課係名、役職名本社採用課担当者○○採用担当電話番号03-1234-5678ＦＡＸ[phone]Ｅメール[e-mail]"」が書かれています。
  
  このような文字列の中で電話番号とFAX番号を抜き出したいのですが、通常10桁が11桁であったり、9桁のときもあるようで苦慮しております。この文字列も一例であり、Eメールがなかったり、表現や文字数も変わってきます。ときには、そもそも電話番号やFAX番号がないこともありますが、そのときはエラーが出てくれれば問題のないので、それ以外のときに桁数に関係なく番号を抽出したいのです。
  
  よろしければ、再度お力をおかしいただけますでしょうか。

  No.4の回答に寄せられた補足コメントです。 補足日時：2020/08/06 10:26

• 毎度まいどありがとうございます。
  前回同様にご教示いただきましたとおりに関数をコピペいたしました。
  当方VBAには明るくないのですが、問題ないと思います。.xlsmで保存もしておりました。
  事例の画像と同じ形式で作り直してA2にも関数を入れ、それをB2にフィルハンドしました。
  保存後、再度い開いてAlt+F11で開くとコピペの関数も残っています。
  
  しかし、結果は番号が出ず、空白になったままでした。
  保存したものを開いたときに、「マクロを有効にする」云々が普段なら入力窓あたりに出てくるのですが、今回はそのようなものが出てこないということは、マクロに関して設定不足のところがあるのでしょうか？
  
  tom04様がつくられたエクセルシートを試してみることは可能でしょうか？

  
  No.6の回答に寄せられた補足コメントです。 補足日時：2020/08/06 21:18

• ご協力ありがとうございます。
  やってみました。
  fxを押しますと「関数の分類」は出てこず、添付画像のように「関数の引数」が出てまいります。
  正確にはわかりませんが、ここには「＝””」となっており、イコールのあとに本来なら電話番号がでているはずなのかな？と感じています。
  でも、原因は全くわかりません。
  現状、このような状態です。
  なにか原因なるものに心当たりはございませんでしょうか。
  
  よろしくお願いいたします。

  
  No.7の回答に寄せられた補足コメントです。 補足日時：2020/08/07 10:54

No.9 ベストアンサー 回答者： tom04 回答日時： 2020/08/07 16:49

最終確認です。

もしかして、C列文字列にスペースが含まれている可能性もありますので、
とりあえずD2セルに
=SUBSTITUTE(SUBSTITUTE(C2,"　","")," ","")

という数式を入れフィルハンドルで下へコピーしておき、D列を参照してみてはどうでしょうか？
（万一C列に全角・半角スペースなどがある場合はスペースなしの文字列になります。）

そしてA1・B1セルにスペースがないというのを確認し、（B1セルは半角大文字のアルファベット）
A2セルに
=bangou($D2,A$1)

という数式を入れフィルハンドルで、列・行方向にコピーしてみてください。

※　これでちゃんと表示された場合はC列にスペースが含まれていた！というコトになりますが、
これでもダメならお手上げです。

どうも失礼しました。m(_ _)m

この回答へのお礼

何度もお手伝いいただき誠にありがとうございました。原因が判明いたしましたのでお礼とともにご報告させていただきます。お伝えしておりました文字列ですが、お知らせしていたものはすべての項目（住所や電話番号、FAX、03-1224など）はあたかも一列に連なっていると思っていたのですが、元データではすべての項目が単語または文節で改行されており、大量のデータのため見易さを優先するため「書式」で「折り返して表示する」を解除しておりました。そのため見た目だけ連続した文字列になっていたのが原因でした。「改行」を削除したことで文字列は一行となり、教えていただきました関数にも反応するようになりました。
何度もお手を煩わせてしまい大変恐縮しております。
本当にありがとうございました。

お礼日時：2020/08/08 15:02

No.8 回答者： tom04 回答日時： 2020/08/07 12:15

またまたお邪魔します。

関数の引数ダイアログボックスを見ると「myStr」の部分で「電話番号」は表示されていますが、
結果が「=""」となっていますね。

こちらでは原因は判りかねますが、
A1セルにスペースなどが含まれていませんか？

こちらで同じようになるのはA1セルの文字列がC列に含まれていない場合に
そうなると思われます。m(_ _)m

この回答へのお礼

ありがとうございます。

お礼日時：2020/08/08 14:44

No.7 回答者： tom04 回答日時： 2020/08/06 21:50

続けてお邪魔します。

お示しの画像を拝見すると、ちゃんと標準モジュールにユーザー定義関数のコードが記載されていますね。
となると、問題ないように思われます。
今一度、手元のExcelで前回のコードを再確認してみました。
こちらではちゃんと電話番号もFAX番号も表示されました。

前回、当方がアップした配置で、A2セルを選択し
数式バーの左側にある、「fx」のアイコンをクリックしてみてください。
「関数の分類」の右側の▼をクリック　→　ユーザー定義を選択　→　「bangou」という関数があるはずなのでそれを選択しOK！
関数のダイアログボックスが表示されますので、「myRng」の欄でC2セルを選択し列固定の絶対参照
「myStr」の欄でA1を選択し行固定の絶対参照し　→　OK！
（通常のワークシート関数を同じやり方です）

これで右へ、下へフィル＆コピーするとちゃんと表示されるはずなのですが・・・

次に
＞保存したものを開いたときに・・・
に関しては一度保存すると次からはメッセージは表示されないと思います。m(_ _)m

この回答へのお礼

ありがとうございます。

お礼日時：2020/08/08 14:44

No.6 回答者： tom04 回答日時： 2020/08/06 16:40

No.2・3です。

No.4さんの補足を拝見するとFAXが全角なのでしょうか？
とりあえずNo.2のコードに少し手を加え、FAXが全角・半角・大文字・小文字関係なく抽出するようにしてみました。
↓の画像のC列はセル内で改行しているだけです。

C列には全角の数字や小文字のアルファベットも混在させています。

ユーザー定義関数のコードを↓に変更してみてください。
（「★」の行に手を加えています）

Function bangou(myRng As Range, myStr As String)
　Dim k As Long
　Dim buf As String
　　k = InStr(StrConv(UCase(myRng), vbNarrow), myStr) '//★//
　　　k = k + Len(myStr)
　　　　Do While Mid(StrConv(myRng, vbNarrow), k, 1) Like "[0-9 -]" '//★//
　　　　　buf = buf & Mid(StrConv(myRng, vbNarrow), k, 1) '//★//
　　　　　k = k + 1
　　　　Loop
　　　bangou = buf
End Function

画像ではA1・B1セルに検索文字を入力しています。（B1セルは大文字の半角です）
A2セルに
=bangou($C2,A$1)

という数式を入れフィルハンドルで列・行方向にコピーしています。

※　保存時は「マクロ有効ブック」として保存する必要があります。m(_ _)m

この回答へのお礼

ありがとうございます。

お礼日時：2020/08/08 14:43

No.5 回答者： d-q-t-p 回答日時： 2020/08/06 11:20

> 電話番号では#N/A

こちらではその例でも問題なく表示されています。

> FAXでは#VALUE
「FAX」が全角だからでは？　実際のデータに合わせて数式は適宜
変更してください。

No.4 回答者： d-q-t-p 回答日時： 2020/08/06 09:37

Excelのバージョンが分からないので汎用的な方法にしときます。

=MID(C1,FIND("電話番号",C1)+4,LOOKUP(,-SUBSTITUTE(MID(C1,FIND("電話番号",C1)+4,{1,2,3,4,5,6,7,8,9,10,11,12}),"-",""),{1,2,3,4,5,6,7,8,9,10,11,12}))

=MID(C1,FIND("FAX",C1)+3,LOOKUP(,-SUBSTITUTE(MID(C1,FIND("FAX",C1)+3,{1,2,3,4,5,6,7,8,9,10,11,12}),"-",""),{1,2,3,4,5,6,7,8,9,10,11,12}))

この回答へのお礼

ありがとうございました。汎用性があり参考になりました。

お礼日時：2020/08/08 14:42

No.3 回答者： tom04 回答日時： 2020/08/05 18:01

No.2です。

こちらの手元のExcelで検証ののちに投稿したので間違いはないと思うのですが・・・

＞A1、B1それぞれのセルにも関数を入力しましたが、反応なしです。

F9キーを押下してみてください。（再計算）
それでも何も表示されませんか？

となるとC1セルの文字列内に
「電話番号」とか「FAX」という文字がないコトが考えられます。
特に「FAX」などは大文字小と小文字では別物と判断されてしまいます。

こちらで考えられるとするとこの程度なのですが・・・m(_ _)m

No.2 回答者： tom04 回答日時： 2020/08/05 16:51

こんにちは！

この質問限定の関数（ユーザー定義関数）を作ってみてはどうでしょうか？

電話番号に関しては携帯電話の場合やハイフンなしの場合にも対応できるようにしてみました。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）
普通の関数のように使ってみてください。

Function bangou(myRng As Range, myStr As String)
　Dim k As Long
　Dim buf As String
　　k = InStr(myRng, myStr)
　　　k = k + Len(myStr)
　　　　Do While Mid(myRng, k, 1) Like "[0-9 -]"
　　　　　buf = buf & Mid(myRng, k, 1)
　　　　　k = k + 1
　　　　Loop
　　　bangou = buf
End Function

画像ではA1セルに
=bangou(C1,"電話番号")

B1セルに
=bangou(C1,"FAX")

という数式をそれぞれ入れています。

※　電話番号が必ずハイフン入りで10桁と決まっているのであれば
No.1さんのようにMID関数をFIND関数で対応できそうですね。m(_ _)m

この回答へのお礼

ありがとうございます。

お礼日時：2020/08/08 14:43

No.1 回答者： iothes00 回答日時： 2020/08/05 16:26

=mid(a1,find("電話番号",a1)+4,10)

=mid(a1,find("FAX",a1)+4,10)

では

この回答へのお礼

ありがとうございました。参考にさせていただきます。

お礼日時：2020/08/08 14:41