Excelで指定した文字数に足りていない場合に指定した箇所に数字を挿入したい 初めて質問させ

Excelで指定した文字数に足りていない場合に指定した箇所に数字を挿入したい




初めて質問させていただきます。Excel初心者です
Excelにて指定した列のデータの文字数を揃えつつ、足りていない場合0を挿入したいのですが隣の列に関数で処理はできないでしょうか

[現状]
A10
A13
A133
A200
B12

↓
[理想]
A010
A013
A133
A200
B012

文字数は最大でも４文字(アルファベット+数字3桁)です。４文字に達していない場合にアルファベットの後に数字の0を入力したいのです。

どうかよろしくお願い致します。

No.1 ベストアンサー 回答者： enunokokoro 回答日時： 2016/11/10 09:28

A列にデータがあるとして、A1から開始しているとします。

B1に　=LEFT(A1,1) & TEXT(MID(A1,2,LEN(A1)),"000")
http://www3.tokai.or.jp/excel/kansu/left.htm
http://www.relief.jp/itnote/archives/003643.php
オートフィルで必要な範囲に設定します。
http://www.eurus.dti.ne.jp/yoneyama/Excel/input_ …

この回答へのお礼

回答ありがとうございます！
画像つきで非常にわかりやすくおかげさまで理想のデータ列を作成できました！

他の方々にもベストアンサーを差し上げたいのですが、一番最初に回答を下さったenunokokoroさんをベストアンサーにさせていただきます、ありがとうございました！

お礼日時：2016/11/10 16:02

No.4 回答者： GooUserラック 回答日時： 2016/11/10 11:39

No.3 の補足

すでに入力されたものを修正するならば、次のコードを標準モジュールに入力して、「修正」を実行してください。
------------------------------------------------------------
Sub 修正()
Dim 行 As Long
Dim 文字列 As String
For 行 = 1 To Cells(Rows.Count, 1).End(xlUp).Row
文字列 = Trim(Cells(行, 1).Value)
If Len(文字列) <= 4 Then
文字列 = StrConv(文字列, vbUpperCase)
If Left(文字列, 1) >= "A" Then
If Left(文字列, 1) <= "Z" Then
If IsNumeric(Mid(文字列, 2)) Then
If Right("00" & Mid(文字列, 2), 3) = Format(Mid(文字列, 2), "000") Then
Application.EnableEvents = False
Cells(行, 1).Value = Left(文字列, 1) & Format(Mid(文字列, 2), "000")
Application.EnableEvents = True
End If
End If
End If
End If
End If
Next
End Sub
------------------------------------------------------------
・対象はＡ列にしていますが、以下の２点３箇所を修正してください。
a.「Cells(Rows.Count, 1).End(xlUp).Row」の「1」を変える。
b.「Cells(行, 1).Value」の「1」を変える。（２箇所あります）
・アルファベットを小文字で入力した場合も大文字に修正しますが、修正したくない場合は以下の２行を修正してください。
a.「文字列 = StrConv(文字列, vbUpperCase)」を削除。
b.「If Left(文字列, 1) >= "A" Then Exit Sub」を「If Left(文字列, 1) >= "a" Then Exit Sub」に変更
・同じ行でも「アルファベット」+「数字３文字以下」以外の文字列には反応しません。

No.3 回答者： GooUserラック 回答日時： 2016/11/10 11:14

別列に書き込む方法はたくさん出ていますので、マクロ（ＶＢＡ）を使って入力時にリアルタイムで修正してしまうものを考えてみました。

以下のコードを対象のシートモジュールに書き込んで下さい。
------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim 文字列 As String
If Target.Column = 1 Then
文字列 = Trim(Target.Value)
If Len(文字列) > 4 Then Exit Sub
文字列 = StrConv(文字列, vbUpperCase)
If Left(文字列, 1) < "A" Then Exit Sub
If Left(文字列, 1) > "Z" Then Exit Sub
If Not IsNumeric(Mid(文字列, 2)) Then Exit Sub
If Right("00" & Mid(文字列, 2), 3) <> Format(Mid(文字列, 2), "000") Then Exit Sub
Application.EnableEvents = False
Target.Value = Left(文字列, 1) & Format(Mid(文字列, 2), "000")
Application.EnableEvents = True
End If
End Sub
------------------------------------------------------------
・対象はＡ列にしていますが「If Target.Column = 1 Then」の「1」を変えれば別の列に出来ます。
・アルファベットを小文字で入力した場合も大文字に修正しますが、修正したくない場合は以下の２行を修正してください。
a.「文字列 = StrConv(文字列, vbUpperCase)」を削除。
b.「If Left(文字列, 1) < "A" Then Exit Sub」を「If Left(文字列, 1) < "a" Then Exit Sub」に変更
・同じ行でも「アルファベット」+「数字３文字以下」以外の文字列には反応しません。

この回答へのお礼

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

マクロも少しずつ覚えていきたいと思っていましたのでマクロでの方法は本当にありがたいです！
今回は関数でのデータ入力をさせていただきましたがマクロでデータそのものを修正するやり方もあるのですね…大変勉強になります！ありがとうございました！

お礼日時：2016/11/10 16:07

No.2 回答者： 銀鱗 回答日時： 2016/11/10 09:36

それなら…

アルファベット1文字を切り取る。
数字「000」＋入力されている数字にする。
右から3文字抜き出す。
先に切り取ったアルファベットを足す。
の操作で可能。

対象がA1セルなら
　=REPLACE(A1,1,1,””)
で、先頭のアルファベットを削除できる。
　=”000” ＆ REPLACE(A1,1,1,””)
で、「000」+「数字」の文字列にできる。
（MID関数で2文字目から最後の文字までを抜き出したものに”000”を加えるというのもありでしょう）
　=RIGHT(”000” ＆ REPLACE(A1,1,1,””),3)
で、数字が3文字に足りない場合は0を前に付けた3桁の数字にできる。
　=LEFT(A1,1) & RIGHT(”000” ＆ REPLACE(A1,1,1,””),3)
で、先頭のアルファベットを加えることができる。

REPLACE関数、RIGHT関数、LEFT関数については、自身で使い方を確認しておいてください。

・・・
LEN関数で文字数を数えて
3だったらアルファベットを「アルファベット+0」にREPLACE関数で置き換え、
2だったらアルファベットを「アルファベット+00」にREPLACE関数で置き換えても良い。
…と思います。
　=IF(LEN(A1)=3,REPLACE(A1,1,1,LEFT(A1,1)＆”0”),IF(LEN(A1)=2,REPLACE(A1,1,1,LEFT(A1,1)＆”00”),A1))
とか。

いろいろなやりかたが考えられます。

この回答へのお礼

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

なるほど様々なやり方があるのですね…
Excelを扱い始めたばかりでまだまだ計算の仕方や関数を使いこなせていませんので別のやり方としてのご提示は大変勉強になりました！

お礼日時：2016/11/10 16:05