エクセルで質問です。外部から文字と半角数字の混じったデータをコピーして

質問

エクセルで質問です。外部から文字と半角数字の混じったデータをコピーしてセルに貼り付ける際、強制的に半角数字だけ入力される、という事はマクロ等を使用すれば可能でしょうか？
例えば、Bの列にはX180、X250、X20という風に、数字以外の文字が混じっています。
これを、セルに入力する際、強制的に180、250、20という風にすることは出来るでしょうか？
言い換えれば、入力後に数字以外の部分を削除するのではなく、入力する際に数字以外を受け付けなくしたいです。

回答No.5

Tofu-Yo

Private Sub Worksheet_Change(ByVal Target As Range)

　　Dim R As Range
　　Dim MyStr As String
　　Dim i As Long

　　For Each R In Target

　　　　If R.Column = 4 And R.Row <> 1 And R.Text <> "" Then

　　　　　　MyStr = ""

　　　　　　For i = 1 To Len(R.Text)

　　　　　　　　If IsNumeric(Mid(R.Text, i, 1)) Then

　　　　　　　　　　MyStr = MyStr & Mid(R.Text, i, 1)

　　　　　　　　End If

　　　　　　Next i

　　　　　　If MyStr = "" Then
　　　　　　　　R.Value = ""
　　　　　　Else
　　　　　　　　R.Value = MyStr + 0
　　　　　　End If

　　　　End If

　　Next R

End Sub

とif文をひとつかませばよいです。

※　蛇足です。Ifの条件のR.Text <> ""は気まぐれで入れました。絶対必要というものではないですが、貼り付けするときに空欄が多い場合には飛ばしてくれるので少しだけ早くなるはずです。
なお、ifのあと３つの条件をandでつなぎましたが、ほんとはifを3重に使った方が少しだけ早くなります。そのとき、なるべくはじかれる数の多い条件を先においた方がより早くなります。

お礼

分かりやすいご説明を付け足していただき、ありがとうございました。
無事解決しました。
スピードに関しては全く気になりません。
おかげさまで効率よく仕事を進める事が出来ます。
本当にありがとうございました。

回答No.4

keithin

>つまりD2～D65535以外は通常どおりのセルにしたいですが、

変更前：
for each h in target.specialcells(xlcelltypeconstants, xltextvalues)

変更後：
for each h in application.intersect(range("D2:D65536"), target).specialcells(xlcelltypeconstants, xltextvalues)

みたいな。

＃言わずもがなですが私の回答したマクロに組み込まないと，上手くありません。

お礼

大変迅速にご回答を頂き、ありがとうございました。
無事解決しました。
おかげさまで効率よく仕事を進める事が出来ます。
本当にありがとうございました。

回答No.3

keithin

>手間を省きながらいかに素早く数値だけが入力された状態にできるか、

こだわりはあくまでそこであって「入力前に」というのは別にどうでもよいなら，普通にマクロでやれば十分です。

手順：
シート名タブを右クリック,コードの表示を選び
現れたシートに下記をコピー貼り付ける。

private sub worksheet_change(byval Target as excel.range)

　dim h as range
　dim i as long
　dim res as string
　on error resume next
　application.enableevents = false　'止めておかないと無限ループします

　for each h in target.specialcells(xlcelltypeconstants, xltextvalues)

　res = ""
　for i = 1 to len(h)
　　if isnumeric(mid(h, i, 1)) then
　　　res = res & mid(h, i, 1)
　　end if
　next i

　if res = "" then
　　h.clearcontents
　else
　　h = val(res)
　end if

　next

　application.enableevents = true
end sub

てなぐあい。

お礼

再びのご回答をありがとうございます。
早速試してみました。
そうです！こんなイメージです。
すみません、先に書けばよかったのですが、
対象としたいのはシート全体では無くて、D1以外のD列全てです。
つまりD2～D65535以外は通常どおりのセルにしたいですが、
そんな事は可能なのでしょうか？
度々すみません。

回答No.2

Tofu-Yo

貼り付けたときにそのようにしたいシートに次のように記述してみてください。（VBE起動後、「プロジェクト」から目的のシートをダブルクリックして、右のウィンドウに貼り付け）

Private Sub Worksheet_Change(ByVal Target As Range)

　　Dim R As Range
　　Dim MyStr As String
　　Dim i As Long

　　For Each R In Target

　　　　MyStr = ""

　　　　For i = 1 To Len(R.Text)

　　　　　　If IsNumeric(Mid(R.Text, i, 1)) Then
　　　　　　　　MyStr = MyStr & Mid(R.Text, i, 1)
　　　　　　End If

　　　　Next i

　　　　If MyStr = "" Then
　　　　　　R.Value = ""
　　　　Else
　　　　　　R.Value = MyStr + 0
　　　　End If

　　Next R

End Sub

ただし、そのシートのどこにどんな入力をしても数字以外除外してしまいます。
特定のエリアだけやりたかったらRの条件を加えないといけないです。

お礼

詳しいご回答をありがとうございます。
早速試してみました。
そうです！こんなイメージです。
すみません、先に書けばよかったのですが、
対象としたいのはD1以外のD列全てです。
つまりD2～D65535以外は通常どおりのセルにしたいですが、
そんな事は可能なのでしょうか？
度々すみません。

回答No.1

keithin

いいえ。ご質問の直接の回答としては，エクセルで何か出来るのは「入力した後」からになります。
例えばマクロを使い，入力「された」際に完全自動でデータを直ちに逐一自動検査し，必要があれば自動で修正させる(修正後のデータを自動で再入力させる）といった具合ですね。

つまり
＞入力後に数字以外の部分を削除するのではなく、入力する際に数字以外を受け付けなくしたいです。

少なくとも検査のために，あなたがマクロのボタンを押してマクロをイチイチ起動してようやくチェックできる，といった手間やタイムラグは，自動化により省略できます。

そういった状況で「本当に入力させたくない」のか，本当に必要な段取りについて再度検討してみてください。

お礼

早々に詳しいご説明を頂き、ありがとうございます。
やはり無理ですか。。。
一旦文字混入の状態でセルへ入力した後で、
マクロを使用するのが無難な路線かもしれませんね。
手間を省きながらいかに素早く数値だけが入力された状態にできるか、
さらなる検討を進めてみようと思います。
ありがとうございました。