電話番号

はじめまして。

①D列には、「10桁数チェック」という名のエラーチェック項目を設け

E列が
0桁、10桁　→　空白
1桁～9桁、11桁以上　→　●　を表示させる

②E列の表示形式　　0###########

➂F列の区分
2 ・0A-BCDE-FGHJ （市外局番1桁＋市内局番4桁地域）
3 ・0AB-CDE-FGHJ （市外局番2桁＋市内局番3桁地域）
4 ・0ABC-DE-FGHJ （市外局番3桁＋市内局番2桁地域）
5 ・0ABCD-E-FGHJ （市外局番4桁＋市内局番1桁地域）

➃G列 ハイフォン付きの電話番号

E列10桁の電話番号をパターン化された区分の「数値」をF列に入力することによって
G列にハイフォン付きの電話番号を表示させる

ただし、『3 ・0AB-CDE-FGHJ （市外局番2桁＋市内局番3桁地域）』のパターンで、
「市外局番2桁」【090】【080】【070】で始まる場合は、
ハイフォン付きの電話番号の前に"(携)△"を付ける

このように上記のルールを満たし、添付画像のようにD列とG列に表示させることが可能な
関数式をどなたか教えて下さい。


Win10 Pro
EXCEL2007

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

• マクロ　VBA 配列数式　作業列　禁止！

    補足日時：2017/03/13 20:35

No.4 ベストアンサー 回答者： yuji3690 回答日時： 2017/03/13 23:06

失礼しました。

中央部分が「6-F2」文字だったのを、6文字→6-1=5文字としたのですが、F2→F2-1文字とする必要もあったので、6-F2→(6-1)-(F2-1)=6-F2で、この部分については元の式のままでよかったわけです。

G2=IF(F2="","",IF(OR(LEFT(E2,2)="90",LEFT(E2,2)="80",LEFT(E2,2)="70"),"(携)","")&"0"&LEFT(E2,F2-1)&"-"&RIGHT(LEFT(E2,5),6-F2)&"-"&RIGHT(E2,4))
ですね

この回答へのお礼

携帯電話番号が11桁であることを忘れていました。
私は、何ておバカさんなんでしょう。
でもyuji3690さんの式で解決することができました。

D列
=IF(F2="","",IF(OR(LEFT(E2,2)="90",LEFT(E2,2)="80",LEFT(E2,2)="70"),IF(LEN(E2)=10," ","●"),IF(LEN(E2)=9,"","●")))

G列
=IF(F2="","",IF(OR(LEFT(E2,2)="90",LEFT(E2,2)="80",LEFT(E2,2)="70"),"(携)"&"0"&LEFT(E2,F2-1)&"-"&RIGHT(LEFT(E2,6),7-F2)&"-"&RIGHT(E2,4),"0"&LEFT(E2,F2-1)&"-"&RIGHT(LEFT(E2,5),6-F2)&"-"&RIGHT(E2,4)))


yuji3690さん
ご親切にありがとうございました。

お礼日時：2017/03/14 06:48

No.3 回答者： yuji3690 回答日時： 2017/03/13 21:52

文字式ではなく、9桁の数字を表現上10桁に見せている。

というわけですね。失礼しました。
であれば、

D2=IF(E2="","",IF(LEN(E2)=9,"","●"))

G2=IF(F2="","",IF(OR(LEFT(E2,2)="90",LEFT(E2,2)="80",LEFT(E2,2)="70"),"(携)","")&"0"&LEFT(E2,F2-1)&"-"&RIGHT(LEFT(E2,5),5-F2)&"-"&RIGHT(E2,4))

というのではどうでしょう？

この回答へのお礼

ありがとうございます。
D2の方は解決しました。

しかしG列は、こちらでは以下のように表示されています。

01-345-6789
012-45-6789
0123-5-6789
01234--6789
(携)090-23-4567
(携)080-23-4567
(携)070-23-4567

どこに問題があるのか私にはわかりません。

お礼日時：2017/03/13 22:04

No.2 回答者： yuji3690 回答日時： 2017/03/13 21:05

D2=IF(E2="","",IF(LEN(E2)=10,"","●"))

「E2が空白なら空白、空白でなくてE2の文字数が10なら空白、どちらでもなければ●」を表示する式です。
ただし、数字かどうかの判定までは入れていませんので、そのようなチェックも必要なら加えてください。

G2=IF(F2="","",IF(OR(LEFT(E2,3)="090",LEFT(E2,3)="080",LEFT(E2,3)="070"),"(携)","")&LEFT(E2,F2)&"-"&RIGHT(LEFT(E2,6),6-F2)&"-"&RIGHT(E2,4))
F2が空白でなかった場合で、E2が090か080か070ならば(携)を付け、E2の左からF2の数字だけ表示し、-を加え、E2の6文字目までで残ったものを表示し、もう一度-を加え、E2の右から4文字を表示する。
という式になっています。
(携)を加える時にFが3かどうかの判定はしていませんが、Fが3以外で090,080,070から始まる番号は無いものと思います。必要があればF=3であるという条件を加えてください。
また、EおよびFは適切な入力がされているという前提ですので、Fが空白の時以外のエラーチェックは含めておりません。Fに2～5以外の入力があった場合や、Eが10桁でなかった場合でも表示されます。

この回答へのお礼

yuji3690さん、ご回答ありがとうございます。そして解説感謝します。

＞ただし、数字かどうかの判定までは入れていませんので、そのようなチェックも必要なら加えてください。
＞IF(LEN(E2)
＞LEN

私は、E列の表示形式を『0###########』としています。先頭の「0」を表示させたいためにそうしました。
従って、この方法では「数値」であって多分「文字列」ではないと思います。
そのために、 yuji3690さんがお示し下さいました式では、こちらでは正しく表示されておりません。
正しく表示させるためには、E列を「数値」から「文字列」に変換させる必要があると思います。
しかしお恥ずかしいことに、「数値」から「文字列」に変換させる方法を私、存じ上げておりません。
その方法を是非教えて下さい。


＞Fが3以外で090,080,070から始まる番号は無いものと思います。

yuji3690さんのおっしゃる通りでした。失礼いたしました。

お礼日時：2017/03/13 21:41

No.1 回答者： runix2007 回答日時： 2017/03/13 20:33

正規表現