Excel 入力条件について

質問

条件が2つある場合の入力設定を教えて頂きました。
セルA1に　12345601　を入力すると　123456-01
セルA2に　1234560001を入力すると　123456-0001　にする。
回答　条件付き書式の数式　=LEN(A1)=10　書式　000000-0000
=LEN(A1)=8　000000-00　
解決済みでしたが　01234501や0001230001　のように頭に0が付くと
上手く行きません。
ご指導をどうぞ宜しくお願い致します。

回答No.6

kichi8000

思った以上に難題ですよ、

１ 頭に”０”がつくと文字列になる。表示形式は文字列に統一する。
２ 条件付き書式の表示形式は数値のみ変換できる。
３ 文字列を数値に変換する場合は元の桁数を保持する必要がある。
　例：00010001　＞ 10001
　　　0000010001　＞ 10001

以上から表示形式だけでは不可能です。

A列に入力、B列に表示、B列を参照なら可能です。

さらに、使い勝手を良くするには若干のVBAとセル幅操作、関数の入力が必要です。

A列のセルの書式設定を文字列にし、インデントを1にする
A1セルに値を入力
A列セル幅を４ピクセルにする
B1セルに
=IF(LEN(A1)=10,TEXT(A1,"0000000-000"),IF(LEN(A1)=8,TEXT(A1,"000000-00"),"-"))
として下方向に必要な分をコピー
該当シートのシート名を右クリックしてコードの表示をクリックし、出たウインドウの白いところに以下のマクロをコピペ

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'指定セル"B2:B10"以外が選択されたら何もしない
If Intersect(Target, Range("B2:B10")) Is Nothing Then Exit Sub
'左のセルに移動
Application.Goto ActiveCell.Offset(0, -1)
End Sub

常にBセルを選択するとカーソルが左に移動し、Aセルの入力に移行します。
表示はBセルになります。
Bセルの編集ではデザインモードアイコンをクリックしてマクロを停止してください。
範囲の変更をする場合はマクロの変更が必要です。

回答No.5

kkkkkm

> 条件付き書式の数式　=LEN(A1)=10　書式　000000-0000

この時の書式設定は数値で有効ですので01234501や0001230001のように文字列になると書式は適応されないと思います。

もしB列とかにデータを表示してもよろしければ

B1に
=IF(LEN(A1)=10,TEXT(A1,"0000000-000"),IF(LEN(A1)=8,TEXT(A1,"000000-00"),A1))
として下方向に必要なけだコピーすれば01234501や0001230001のような文字列が入っても大丈夫です。

また、データそのものを書き替えてよろしければマクロで対応できます。
該当シートのシート名の所を右クリックしてコードの表示をクリックして出た右の白いところに以下のコードをコピペすると、A1からA100までのセルが編集された場合、データを「-」付に書き替えます。
8桁もしくは10桁以外、または既に「-」が付いているものは書き替えません。
有効範囲の変更はRange("A1:A100")のA1:A100を書き替えて下さい。

Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(Target.Value, "-") > 0 Or (Len(Target.Value) <> 8 And Len(Target.Value) <> 10) Or _
Application.Intersect(Target, Range("A1:A100")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
If Len(Target.Value) = 8 Then
Target.Value = Format(Target.Value, "000000-00")
ElseIf Len(Target.Value) = 10 Then
Target.Value = Format(Target.Value, "000000-0000")
End If
Application.EnableEvents = True
End Sub

回答No.4

masnoske

数値で入力したら頭に 0が付かない．
文字列で入力したら 条件付き書式が機能しない．
数値か文字列以外に入力方法はないのですから，条件付き書式では不可能というのが答えです．

A列に入力した数値をB列に表示するのであれば数式で可能です．
(1) A1セルと A2セルの書式を文字列に設定（頭に 0を入力できるようにする）．
(2) B1セルに以下の数式を入力
　=LEFT(A1,LEN(A1)-2) & "-" & RIGHT(A1,2)
(3) B2セルに以下の数式を入力
　=LEFT(A2,LEN(A2)-4) & "-" & RIGHT(A2,4)

どうしてもフォームを変更できない場合は，データ入力を別シートにして上記の設定をし，結果を参照させる．

回答No.3

readme_eml

セルの書式設定で文字列を選択すればどうでしょうか？

アポストロフィーなしで保存できます。

補足

お忙しい中、ご回答を有難うございます。
当初の質問にある　条件付き書式　数式 =LEN(A1)=10　書式　000000-0000 でハイフンが自動入力された数字 例)123456-0001の 「セルの書式設定」を文字列に変更をすると1234560001になりハイフンが消えてしまいました。
お時間がある時に又ご回答を下さいますと有難いです。
どうぞ宜しくお願い致します。

回答No.2

imogasi

これでは、条件を考えるのがむ つかしいと思う。
質問者自身しか判らないことなので、しっかり説明すべきだ。
また、エクセルには、入力規則という機能はあるが、入力条件という言葉は、一般用語しか使わないと思う。
文章で説明すべきだ。２例ぐらいでは部外者には判らない。
文字列の長さが頼りにならないとなると01と0001のタイプの違いは
どう考えるのか？０１や０００１のように０が来るという条件もダメだろうし。
サブ番号部は２３（０が現れないもの）なんてのもあるのでしょう。
001（３桁）タイプもあり得るのか？
ーー
関数にしろプログラムにしろ、文章で表せないものは、難しいのだ。
コンピュータを使いながら、この文章での、明確化（説明）が出来てない質問が多くて、読者・回答者としては困っている。

補足

imogasi様
説明が不十分の為、ご回答にお困りで申し訳ありません。
下記の場合の入力設定を教えて下さい。
A列に番号を入力していきます。
セルA1に 12345601 を入力すると 123456-01(6桁-2桁)
セルA2に 1234560001を入力すると123456-0001(6桁-4桁)　ハイフンが入るようにしたいです。
頭6桁には000123や012345等0が付く場合があります。
ご指導をどうぞ宜しくお願い致します。

回答No.1

aokii

01234501や0001230001　のように頭に0が付くデータをセルに入力する時は、’01234501や’0001230001　のように頭に’（アポストロフィー）を付けて入力して、Excel がセルのデータを数字ではなく、文字列として認識できるようにしてみてください。

補足

aokii様
ご回答を有難うございます。
'(アポストロフィー)を付けずに、文字と判別する方法は
他にはないのでしょうか。
入力表として共有する為、'を付けるひと手間を出来れば無くしたいと考えております。
お忙しい所申し訳ございませんが、今一度ご回答を下さいます様、どうぞ宜しくお願い致します。