Excelでセル内の数字だけ取り出したい。

質問

1セル内に最大40文字程度の半角英数カナが混在しています。
例：1234abcdアイウ3456efghijk

これが数千行あるのですが、集計のため数字にしたく、
しかも数字連続と次の数字連続の間にスペースを入れたいです。
例：1234 3456

かなり調べましたが、関数では数字を取り出すだけでも40文字は不可能のように思います。

マクロで置き換えを連続させて文字を消すのは思いつくのですが、
今少しスマートな方法はありますでしょうか？

回答No.2

mu2011 · ベストアンサー

ＶＢＡの一例です。
仮にＡ列にデータがある事で数字に上書きしますので元データは他シートにサーブして下さい。
対象シートのタブ上で右クリック→コードの表示→以下のコードを貼り付け→F5キー押下

Sub sample()
Set RE = CreateObject("VBScript.RegExp")
With RE
.Pattern = "[A-zア-ンア-ン]+"
.IgnoreCase = True
.Global = True
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 1) = Trim(.Replace(Cells(i, 1), " "))
Next
End With
End Sub

お礼

完璧でした。
ありがとうございます。

回答No.6

tom04

No.3です！
たびたびごめんなさい。

投稿した後、気づきました。
＞数字連続と次の数字連続の間にスペースを入れたいです・・・
の部分を見逃していました。

前回の投稿は無視してください。

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

お礼

ご丁寧にどうも(＾＾)

回答No.5

keithin

ALT+F11でVBE画面を出し
標準モジュールを挿入し
下記をコピー貼り付ける

public function getnums(byval a as variant) as variant
　dim i, res
　for i = 1 to len(a)
　　if isnumeric(mid(a, i, 1)) then
　　　res = res & mid(a, i, 1)
　　else
　　　res = res & " "
　　end if
　next i
　getnums = application.trim(res)
end function

A1セルに「1234abcdアイウ3456efghijk」のようにあるとすると，
=getnums(A1)
のようにする。

お礼

ご丁寧にありがとうございました。
とりあえずNo2の方のマクロが簡単で採用させていただきました。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＃１です。計算速度改良版、C1セルに
=IF(B$1="","",IF(B$1>LEN(A1),TRIM(C1),IF(ISNUMBER(MID(A1,B$1,1)*1),C1&MID(A1,B$1,1),C1&" ")))
文字数分検査すればよいですからね。

回答No.3

tom04

こんにちは！
すでに回答は出ていますので、参考程度で・・・

A列のデータをB列に表示するようにしてみました。
データは1行目（A1セル）からあるとします。

Sub test()
Dim i, k As Long
Dim str, buf As String
Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To Len(Cells(i, 1))
str = Mid(Cells(i, 1), k, 1)
If str Like "[0-9]" Then
buf = buf & str
End If
Next k
Cells(i, 2) = buf
buf = ""
Next i
Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub

※　一文字ずる舐めるように検索していますので、若干時間がかかるかもしれません。
参考になれば良いのですが・・・m(_ _)m

お礼

ありがとうございました。
とりあえずNo2の方ので一発でした。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

＞例：1234 3456
も計算に向いていないと思うのですが良いのでしょうか？

また、数字は最大何か所ちりばめられているのでしょうか？

とりあえず、プログラム的な数式の方法で、
A1セルに対象の文字列
ツール-オプション [レ]反復計算 反復回数 100
C1セルに
=IF(B$1="","",IF(B$1>=100,TRIM(C1),
IF(ISNUMBER(MID(A1,B$1,1)*1),C1&MID(A1,B$1,1),C1&" ")))
下へオートフィル

B1セルに トリガー
=B1+1

お礼

すみません、循環と出てしまいました…