プロが教える店舗&オフィスのセキュリティ対策術

はじめまして。

①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

A 回答 (4件)

失礼しました。

中央部分が「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))
ですね
    • good
    • 1
この回答へのお礼

携帯電話番号が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

文字式ではなく、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))

というのではどうでしょう?
    • good
    • 0
この回答へのお礼

ありがとうございます。
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

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桁でなかった場合でも表示されます。
    • good
    • 0
この回答へのお礼

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

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

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


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

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

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

正規表現

    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!