[[20210114071559]]　『データの入力規則で電話番号のハイフン(-)を自動刀x（お正月）　ページの最後に飛ぶ

『データの入力規則で電話番号のハイフン(-)を自動入力』（お正月）

ご質問させていただきます。
データの入力規則で電話番号のハイフン(-)が自動で入るように設定したいと思っています。数字のみを入力すると、携帯番号であれば11桁(000-0000-0000)、固定電話であれば10桁(0000-00-0000)にするというものです。固定電話の形は特定の地元地域の番号であるため、これで固定して構いません。
以下のような関数をユーザー設定の数式に設定して実行していますが、入力不可となってしまいます。
どうか、お力添えのほどお願いいたします。

=IF(LEN(G18)=10,TEXT(G18,"0000-00-0000"),IF(LEN(G18)=11,TEXT(G18,"000-0000-0000"),""))

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 入力規則は自動入力ではなく、入力を制限するものです
 条件付き書式で条件を3種類作って、「見た目だけ」ハイフンが挿入されたようにする方法が簡便かと思います
 実際の値もいじるとなると、マクロのchangeイベントがよいかと

 どちらが希望ですか？
（稲葉） 2021/01/14(木) 07:56

 表示形式の[その他]のところの、電話番号(東京)は、
  [<=99999999]####-####;(00) ####-####
 となってます
 これを参考にして、
 [<=99999999]####-####;[<=999999999]0000-##-####;000-####-####
 とかにすればいいのではないでしょうか
（´・ω・`） 2021/01/14(木) 09:08

稲葉さん、ありがとうございます。
私の希望としては、直接そのセルに番号のみを入力したら、ハイフンが自動でつくようにしたいのです。
別のセルにハイフンが表示されるという形ではありませんので、どちらがベストなのでしょうか？逆の質問でごめんなさい。

´・ω・`さん、ありがとうございます。
早速、試してみました。
しかし、入力したはずの先頭の0が実際には入力されていません。
これを入力したとおりにしたいのです。
今一度よろしくお願いいたします。
（お正月） 2021/01/14(木) 09:15

 [<=99999999]0000-00-0000;000-0000-0000  でどうですか。
 　
（tora） 2021/01/14(木) 09:43

toraさん、ご返事ありがとうございます。
早速、試してみましたが、思った結果になりません。やはり、先頭の0が入りません。
固定電話であれば、「0999-99-9999」の結果を求めたいところが、「009-9999-9999」となってしまいます。

（お正月） 2021/01/14(木) 10:09

 >入力したはずの先頭の0が実際には入力されていません

 ハイフンを入れないで入力したデータは数値データなので
 実データに頭の0を残すことはできません。

 数字だけ入力して実際のデータもハイフンや頭の0を入れることはおそらくマクロでしかできません。
 一般機能の範囲でやるなら、

 ・見た目だけ希望通りにする
 ・別セルに表示する
 ・文字列でハイフンも含め入力する

 などになります。
 ただし、入力して後から見た目通りのデータに変換するということはできます。
（コナミ） 2021/01/14(木) 10:12

 ＞私の希望としては、直接そのセルに番号のみを入力したら、ハイフンが自動でつくようにしたいのです。 
 であればマクロで対応になるかと思います。

 シートモジュールにこんな感じ？　初期設定は、A1:A10が対象です。
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ans As String
        Dim rngHyphen As Range
        Set rngHyphen = Range("A1:A10") 'セルの書式設定を文字列にする

        If Target.CountLarge > 1 Then Exit Sub
        Select Case Len(Target.Value)
            Case 11: ans = Format$(Target.Value, "000-0000-0000")
            Case 10: ans = Format$(Target.Value, "0000-00-0000")
        End Select
        If ans <> "" Then
            Application.EnableEvents = False
            Target.Value = ans
            Application.EnableEvents = True
        End If
    End Sub

（稲葉） 2021/01/14(木) 11:26

稲葉さん、ありがとうございます。
早速、試してみましたが、文字列にはならず、先頭の文字が消えた状態の数値がセルに入ります。ハイフンも付きません。
（お正月） 2021/01/14(木) 11:37

あまりVBAは得意ではないので間違っていたら申し訳ないのですが、

        Dim rngHyphen As Range
        Set rngHyphen = Range("A1:A10") 'セルの書式設定を文字列にする
書式設定は変わっているんですかね？

試しにこの次列に以下を入れたらうまく動いたように見えます
　　　　rngHyphen.NumberFormatLocal = "@"

見当違いの事を言っていたらすみません･･･
（qwerty） 2021/01/14(木) 11:58

qwertyさんのご指示どおり、rngHyphen.NumberFormatLocal = "@"を追加したところ、携帯番号も固定番号もきちんと先頭の0もハイフンも入りました！ありがとうございました！
稲葉さん、みなさん、どうもありがとうございました。また、よろしくお願いします。
（お正月） 2021/01/14(木) 12:24

同じシートの該当セル以外に日付などを入力しているセルまでも同じハイフンの形式に変わってしまいます。
指定のセルのみこの形式にしたいのです。よろしくお願いします。
（お正月） 2021/01/14(木) 15:09

 ＞Range("A1:A10") 
 ここ変えてください
（稲葉） 2021/01/14(木) 15:11

はい、もちろん変えています。その結果がそうなってしまいます。
（お正月） 2021/01/14(木) 15:20

 >Select Case Len(Target.Value)
 >    Case 11: ans = Format$(Target.Value, "000-0000-0000")
 >    Case 10: ans = Format$(Target.Value, "0000-00-0000")
 >End Select

 ↑の前に、↓を追加でどうでしょうか？
 If Intersect(Target, rngHyphen) Is Nothing Then Exit Sub

（虎） 2021/01/14(木) 15:39

虎さん、ありがとうございます。
日付もきちんと表示できるようになりました。
ありがとうございました。
（お正月） 2021/01/14(木) 15:47

 Oh・・・
 抜けてて申し訳ございませんでした。

 虎さんありがとうございました。
（稲葉） 2021/01/14(木) 17:51

夜分遅くにすみません。
以下のエラーが出てしまいます。
自分なりに調べてみたのですが、対応策がわからず、、、

RangeクラスのNumberFormatLocalプロパティを設定できません。
（お正月） 2021/01/20(水) 22:44

以下がデバッグ表示されます。
rngHyphen.NumberFormatLocal = "@"
（お正月） 2021/01/21(木) 07:09

 1/14には出来たんでしょ？
 シートの保護をかけたりしませんでしたか？
（とおりすがり） 2021/01/21(木) 07:13

とおりすがりさん、ありがとうございます。
はい、その後確かに保護をかけました。保護を外すと出ません。
ただ、このデータは複数人が使用することを予想していますので、出来れば保護をかけたいてす。
（お正月） 2021/01/21(木) 07:16

そういうことであれば、ブックを開いたときに一旦シート保護を解除して、すぐに、UserInterfaceOnly:=Trueにして保護するとよいと思います。

http://officetanaka.net/excel/vba/sheet/sheet07.htm
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_130_04.html

（もこな２） 2021/01/21(木) 07:58

コメント返信：

[ 一覧(最新更新順) ]

[[20210114071559]] 『データの入力規則で電話番号のハイフン(-)を自動刀x（お正月） ページの最後に飛ぶ

[[20210114071559]]　『データの入力規則で電話番号のハイフン(-)を自動刀x（お正月）　ページの最後に飛ぶ