以下のように、一つのセルに、数字がカンマで区切られて入力されているデータが数万件あります。
A列1行目
「10, 20, 30, 40, 50, 80, 110, 120, 130」
A列2行目
「10, 20, 40, 80, 110, 130」
A列3行目
「50, 100, 110, 120, 130」
●それぞれ、2桁の数字を、始めに「0」をつけて、3桁にしたいです。
●セルは全て文字列設定になっています。
●「10」を検索し、「010」に置換えようとしたら、「110」も検索対象になってしまい、「1010」になってしまいました。
★完成させたい答え
1行目
「010, 020, 030, 040, 050, 080, 110, 120, 130」
2行目
「010, 020, 040, 080, 110, 130」
3行目
「050, 100, 110, 120, 130」
関数などで短時間で出来る方法を教えて下さい。よろしくお願いします。
No.1
- 回答日時:
10を検索するのではなく
0,を検索して0,0に置き換えては如何でしょうか?
ワイルドカードでいうところの「1文字」を表す「.」を使って
.,.→.,0.
でも良いかもしれませんね
No.2
- 回答日時:
以下の手順でいかがでしょうか?
新しいブックのA列にコピー
A列を選択して、メニューのデータ→区切り位置でカンマにて分ける
数字の入ったセルを含む範囲(全体でもいいです)を選択して、右クリック→セルの書式設定を選択
表示形式タブでユーザー定義を選択、右側に"000"と入力
名前を付けて保存 ファイルの種類からCSV(カンマ区切り)を選択
メモ帳などで開く
No.4
- 回答日時:
1.Alt+F11を押す。
2.VBAの画面になるので、右側の画面に以下のマクロをコピーして貼り付ける。
Sub format3keta()
Dim r As Range
Dim s As Variant
Dim i As Integer
Dim NewStr As String
For Each r In Range("A1", Range("A65536").End(xlUp))
s = Split(r.Value, ",")
NewStr = ""
For i = 0 To UBound(s)
If i = 0 Then
NewStr = Format(s(i), "000")
Else
NewStr = NewStr & "," & Format(s(i), "000")
End If
Next
r.Value = NewStr
Next
End Sub
3.そのままF5キーを押す。
4.Excelの画面に戻ると3桁に揃っているはず。
ご回答ありがとうございました。
一度に出来て感激しました。(VBAを勉強していないので意味があまり理解出来ていないのですが・・)
1つ、再度質問させてください。
このマクロだと、1列のみの実行だと思います。
いくつもの列に対して実行したい場合は、VBA画面を開き、列番号を変更して実行(F5)する事を何度か行うしか方法はないでしょうか?
たびたびの質問ですみませんがよろしくお願いします。
No.5
- 回答日時:
◆関数による回答ですが、どうしても作業列が必要です
A B C D
1 10, 20, 30, 40, 50, 80, 110, 120, 130 010, 020, 030, 040, 050, 080, 110, 120, 130 8 6
2 10, 20, 40, 80, 110, 130 010, 020, 040, 080, 110, 130 5 4
3 50, 100, 110, 120, 130 050, 100, 110, 120, 130 4 1
C1=LEN(A1)-LEN(SUBSTITUTE(A1,",",))
D1=SUMPRODUCT(N(MOD(FIND("♪",SUBSTITUTE(" "&A1,",","♪",ROW(INDIRECT("1:"&C1)))),4)=0))
B1=MID(SUBSTITUTE(LEFT(" "&A1,FIND("♪",SUBSTITUTE(" "&A1,",","♪",(D1))))," "," 0"),2,LEN(A1))&REPLACE(A1,1,FIND("♪",SUBSTITUTE(" "&A1,",","♪",(D1)))-1,)
★共に下にコピー
ご回答、ありがとうございました。
ある程度は正しい答えが出たのですが、完璧ではなかったので、再度質問させて下さい。
10, 30, 50, 70, 80, 100, 110, 130, 140, 160
↓
010, 030, 050, 070, 080, 0100, 0110, 130, 140, 160
上記のように、100と110のみ、4桁になってしまいます。
※ただ、絶対4桁になるわけでもありません。3桁のまま正しい答えが出るものもあります。
申し訳ありませんが、よろしくお願い致します。
No.6ベストアンサー
- 回答日時:
No.4です。
失礼しました。データが入っているのはA列だけではないのですね。
それでは以下のようにマクロを上書きしてください。
データが数万件もあると、かなり時間がかかるかもしれません。
なにか不具合が起こったときのために、元のファイルのバックアップはとっておいた方がよいでしょう。
Sub format3keta()
Dim r As Range
Dim s As Variant
Dim i As Integer
Dim NewStr As String
For Each r In ActiveSheet.UsedRange
If r.Value <> "" Then
s = Split(r.Value, ",")
NewStr = ""
For i = 0 To UBound(s)
If i = 0 Then
NewStr = Format(s(i), "000")
Else
NewStr = NewStr & "," & Format(s(i), "000")
End If
Next
r.Value = NewStr
End If
Next
End Sub
早急なご対応、本当に感謝しています。業務が止まらず作業出来、助かりました。そして、本当、たびたびで申し訳ないのですが、あと1点教えて頂けますでしょうか。
今回お教えいただいたのは、それぞれのNoの前に「0」をつけるものでしたが、今度は、後ろに「0」をつける方法もお教え頂けますか。
●
A列1行目
「01, 02, 03, 04, 05, 08, 110, 120, 130」
A列2行目
「01, 02, 04, 08, 110, 130」
A列3行目
「05, 100, 110, 120, 130」
★完成させたい答え
1行目
「010, 020, 030, 040, 050, 080, 110, 120, 130」
2行目
「010, 020, 040, 080, 110, 130」
3行目
「050, 100, 110, 120, 130」
※違うパターンのデータを見つけてしまい困ってしまいました。マクロのどこかを変えればいいのだと思い考えてみたのですが、意味を理解していない為、修正出来ませんでした。
よろしくお願い致します。
No.7
- 回答日時:
修正してみました。
「データが2桁で0から始まる場合、後ろに0を付け足す」という処理を入れています。
他のパターンはないですか?1桁の場合とか...
Sub format3keta()
Dim r As Range
Dim s As Variant
Dim i As Integer
Dim NewStr As String, Val As String
For Each r In ActiveSheet.UsedRange
If r.Value <> "" Then
s = Split(r.Value, ",")
NewStr = ""
For i = 0 To UBound(s)
If Len(s(i)) = 2 And Left(s(i), 1) = "0" Then
Val = s(i) & "0"
Else
Val = Format(s(i), "000")
End If
If i = 0 Then
NewStr = Val
Else
NewStr = NewStr & "," & Val
End If
Next
r.Value = NewStr
End If
Next
End Sub
何度も申し訳ありません。
説明が不足していました。
「2桁で0から始まる場合のみ」ではなく、「2桁のものは全て後ろに0をつける」が必要です。
全く違う経路からきたデータがあるとわかったのです。
よって、さきほど教えて頂いたもの(2桁のNoの前に「0」をつけ、3桁にする)とは別に、「2桁のNoの後ろに「0」をつける」を新しく教えて頂ければと思います。
よろしくお願いします。
No.8
- 回答日時:
なるほど、前とは別のデータで、別の方法で処理したいということですね。
それでは、そっち専用のマクロが必要ですね。ちょっとの修正でできました。最初のマクロとは別の処理をするので、マクロの名前も変えてあります(2と名前の後ろにつけただけですが)Sub format3keta2()
Dim r As Range
Dim s As Variant
Dim i As Integer
Dim NewStr As String, Val As String
For Each r In ActiveSheet.UsedRange
If r.Value <> "" Then
s = Split(r.Value, ",")
NewStr = ""
For i = 0 To UBound(s)
Val = Left(Trim(s(i)) & "000", 3)
If i = 0 Then
NewStr = Val
Else
NewStr = NewStr & "," & Val
End If
Next
r.Value = NewStr
End If
Next
End Sub
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) EXCEL VBA 単語置き換え について質問です ブック名 ぶぶぶ シート名 ししし セル V3〜 3 2023/03/08 01:41
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Visual Basic(VBA) VBA 改行コードの取り方 1 2022/03/22 14:14
- その他(Microsoft Office) ある表(10桝程度)の中に数字が入っています。ダブっている数字を除く数字の合計数の計算方法 5 2023/02/15 11:33
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
- Excel(エクセル) Excelでの検索結果を含む行だけを表示させたい 5 2023/03/10 17:08
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル マクロ オートフィ...
-
excel 小さすぎて見えないセル...
-
excelのデータで色つき行の抽出...
-
エクセルで特定の文字列が入っ...
-
【Excel関数】UNIQUE関数で"0"...
-
結合されたセルをプルダウンの...
-
[EXCEL]ボタン押す→時刻が表に...
-
エクセル 上下で列幅を変えるには
-
アクティブになっている行をマ...
-
特定の文字がある行以外を削除...
-
AのセルとB行を比較して、一致...
-
Excel グラフのプロットからデ...
-
EXCELで最後の行を固定
-
セルの色によって条件文をつけ...
-
エクセル マクロ等を利用した各...
-
Excel2007で、指定範囲の行高さ...
-
エクセル マクロで数値が変っ...
-
Excelマクロ 期間を指定してデ...
-
貼り付けた数式を最終行まで繰...
-
Excel VBA アクティブセルから...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで特定の文字列が入っ...
-
エクセル マクロ オートフィ...
-
【Excel関数】UNIQUE関数で"0"...
-
[EXCEL]ボタン押す→時刻が表に...
-
結合されたセルをプルダウンの...
-
エクセル マクロで数値が変っ...
-
Excel グラフのプロットからデ...
-
AのセルとB行を比較して、一致...
-
エクセル 上下で列幅を変えるには
-
Excel ウインドウ枠の固定をす...
-
特定の文字がある行以外を削除...
-
excelのデータで色つき行の抽出...
-
エクセル2016で時間を入力して...
-
excel 小さすぎて見えないセル...
-
EXCELで最後の行を固定
-
エクセルVBA 最終行を選んで並...
-
VBAで色の付いているセルの行削除
-
エクセルマクロで偶数行(又は...
-
エクセルのセルに指定画像(.jpg...
-
罫線の斜線を自動で引くマクロ
おすすめ情報