EXCEL セルの表示形式について

質問

EXCELでセルに３文字のアルファベットと数字を入力した場合、その文字間にハイフン「-」を表示させたいと思います。

入力時に文字列として直接入力すればいいのでしょうが、入力簡略の為ハイフンを自動で表示できないものかと、お伺いいたしました。

数字だけならユーザー定義で、＃”-”＃”-”＃で出来るのですが、文字が入ると入力した通りにしか表示されません。

何かいい方法をご存知ならご教授下さい。

例：　入力 → 49Q　　　セルの表示 → 4-9-Q

宜しくお願いいたします。

回答No.4

tom04 · ベストアンサー

こんにちは！

＞入力簡略の為ハイフンを自動で表示できないものかと
と
＞表示形式で処理したい！
というコトを考慮すると、入力したセルそのものがそのようになって欲しい！というコトですね？

VBAになってしまいますが一例です。
セル範囲が限定できませんのでA列すべてを対象としてみました。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り、A列でデータ入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim k As Long, str As String
'▼A列限定
If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If Len(.Value) = 3 Then
If StrConv(.Value, vbNarrow) Like "*" & "[0-9]" & "*" Then
If StrConv(.Value, vbNarrow) Like "*" & "[a-z A-Z]" & "*" Then
str = Left(.Value, 1) & "-" & Mid(.Value, 2, 1) & "-" & Right(.Value, 1)
.Value = str
End If
End If
End If
End With
End Sub 'この行まで

※　3文字でアルファベットと数字が存在するデータが入力された場合だけ反応します。
どちらか一方だけ（アルファベットのみや数字のみ）の場合は反応しません。m(_ _)m

回答No.5

tom04

No.4です。
投稿後気づきました。

コード2行目
＞Dim k As Long, str As String
の
＞k As Long,
は不要です。

＞Dim str As String
だけにしてください。

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

お礼

ご回答ありがとうございます。

直接セルで対応するものだと思っていましたが、そういう方法もあるんですね。

勉強不足です。

コードをご丁寧に書いて頂いて大変助かりました。
コピーで貼り付けたコードの入力列とＲＡＮＧＥの範囲を変更して、ようやく表示させる事に成功しました。

ただ、数字だけだと反応しないと書いてあったのでやっぱり両方は無理なのかな思いながら、それ以外にユーザー定義で #"-"#"-"# に変更したところ、数値のみでも表示する事ができました。

おかげでスッキリしました。

まあ、面倒くさがらずに文字列で直接入力していれば苦労はしなかったのですが…。

いい勉強になりました。

ありがとうございました。

回答No.3

mshr1962

>やはりＭＩＤ関数なんでしょうかね…。
別の関数でもできますよ。

=REPLACE(REPLACE(A1,3,0,"-"),2,0,"-")

または

=REPLACE(REPLACE(A1,2,0,"-"),4,0,"-")

前のは、3文字目の前に"-"を追加後に、2文字目の前に"-"を追加
後のは、2文字目の前に"-"を追加後に、1文字ずれた4文字目の前に"-"を追加
どちらもREPLACE関数での置換文字数を0にすることで文字の追加をしています。

お礼

ご回答ありがとうございます。

同じ作業でも色々な関数があるんですね。
勉強になります。

ありがとうございました。

回答No.2

aokii

入力したセルを直接表示変更すると、ややこしいので、

A1セルに49Qを入力した場合、B1セルに以下の式を入れて、

=MID(A1,1,1)&"-"&MID(A1,2,1)&"-"&MID(A1,3,1)

でいかがでしょう。

お礼

ご回答ありがとうございます。

No.1の方のご回答もそうでしたが、やはりＭＩＤ関数なんでしょうかね…。

ほかに手立てが無ければ、別セルで式を入れてやってみます。

ありがとうございました。

回答No.1

gyouda1114

A1に「49Q」
１．右に一列挿入
２．挿入した列に
=MID(A1,1,1) & "-" & MID(A1,2,1) & "-" & MID(A1,3,1)
３．下方にオートフィル
４．挿入した列をコピー
５．その列に形式を選択して貼り付け → 値
６．A列を削除

これじゃダメか

お礼

早速のご回答ありがとうございます。

そちらの方法も考えたのですが、もしかしたら書式設定のユーザー定義で表示させられる方法があるかもと思って質問させていただきました。

無ければ最終的にその方法かな…とも思います。

ありがとうございました。