エクセルで数字とアルファベットと文字列を分割する方法

質問

こんばんは。
エクセルのセルで数字とアルファベットの文字列を分割する方法を教えて下さい。

100AB3.4C → "100" "AB" "3.4" "C"

間にスペースやカンマなど無く、文字数は異なります。
数字は小数点がある場合と無い場合があります。（上記の "100" "3.4" のように）
同じ列に同様のデータが数件～数千件あり、まとめて分割したいです。

関数もしくはマクロ(VBA)で可能であれば教えて下さい。

最近マクロの勉強を初めたのですが、方法が思いつきません。。。
宜しくお願いします。

回答No.6

noname#52504 · ベストアンサー

#2=#3です。

●ユーザー定義関数版

動作の概要
　第１引数で指定した文字列を、数字と文字に分割し、
　第２引数で指定した番号の文字列を返す
　ex.　=Sample2("100AB3.4C",3)　⇒　"3.4"

　A列の文字列についてB,C,D…列に返すのであれば、
　B1セル：=Sample2($A1,COLUMN()-1)
　として右方・下方にフィル

'----------------↓　ココカラ　↓----------------
Function Sample2(myStr, myIdx)
　Dim a() As String
　f = IsNumeric(Left(myStr, 1))
　j = 1
　ReDim a(j)
　For i = 1 To Len(myStr)
　　t = Mid(myStr, i, 1)
　　If (f <> IsNumeric(t)) And Not (f And t = ".") Then
　　　f = Not f
　　　j = j + 1
　　　ReDim Preserve a(j)
　　End If
　　a(j) = a(j) & t
　Next i
　If myIdx <= j Then
　　Sample2 = a(myIdx)
　Else
　　Sample2 = ""
　End If
End Function
'----------------↑　ココマデ　↑----------------

●Subプロシージャ版

動作の概要
　A列1行目以降の文字列を、
　数字と文字に分割し、B列以降に転記する

'----------------↓　ココカラ　↓----------------
Sub Sample3()

　d = "A1" '元データの最初のセル
　r = "B1" '結果表示範囲の最初のセル

　Dim a() As String
　For k = Range(d).Row To Cells(65536, Range(d).Column).End(xlUp).Row
　　ReDim a(1 To 1)
　　myStr = Cells(k, Range(d).Column)
　　f = IsNumeric(Left(myStr, 1))
　　j = 1
　　ReDim a(1 To j)
　　For i = 1 To Len(myStr)
　　　t = Mid(myStr, i, 1)
　　　If (f <> IsNumeric(t)) And Not (f And t = ".") Then
　　　　f = Not f
　　　　j = j + 1
　　　　ReDim Preserve a(1 To j)
　　　End If
　　　a(j) = a(j) & t
　　Next i
　　Cells(k, Range(r).Column).Resize(1, j) = a()
　Next k
End Sub
'----------------↑　ココマデ　↑----------------

いずれもExcel2003で動作確認済。
ただし、最低限の記述しかしていないので、
コーディングは参考にしない方がよろしいかと。

不明な点も多いと思いますし、
ご要望に沿わない点もあるかもしれませんが、
これ以上は完全に作業になってしまいますから、
最終回答ということにさせてください。

■Excelでお仕事！「VBA基本」メニュー
http://www.asahi-net.or.jp/~ef2o-inue/menu/menu04.html

お礼

回答本当に有難うございます。
上手く出来ました。
サイトも教えて頂き有難うございます。
サイトは印刷して通勤途中に読んでます。
他にも色々やりたい事があるので、勉強がんばりたいと思います。

回答No.5

nora1962

NO1さんも書かれているのですが、「ツール」→「参照設定」で
「Microsoft VBScript Regular Expressions　5.5」チェックしてください。

参考URL：

http://codezine.jp/a/article/aid/1655.aspx

お礼

回答有難うございます。
正規表現、出来ました ^^
それで教えて頂いた内容でエラーにはならないのですが
上手く実行出来ませんでした。。。
例えば問題の内容"100AB3.4C"をA1セルに入力して、B1セルから分割内容が表示されるようにしたいのですが。
度々申し訳ございません。
もし良かったらもう少し教えて頂けると嬉しいです。

回答No.4

nora1962

No1さんと同じく正規表現ですが
Sub test()
Dim s As String
s = "100AB3.4C99"

Dim re As New RegExp
Dim mc As MatchCollection
Dim m As Match
Dim i As Long, j As Long
Dim t As String
re.Global = True
re.Pattern = "([0-9.]*)([^0-9.]*)"
Set mc = re.Execute(s)
If mc.Count <> 0 Then
For i = 0 To mc.Count - 1
Set m = mc.Item(i)
For j = 0 To m.SubMatches.Count - 1
If m.SubMatches(j) <> "" Then
t = t & """" & m.SubMatches(j) & ""","
End If
Next
Next
t = Mid(t, 1, Len(t) - 1)
End If
Debug.Print t
End Sub

お礼

回答有難うございます。
VBAと正規表現とは違うのでしょうか？
エクセルのマクロに教えて頂いた内容を入力してみたのですが、エラーになってしまいました。
ちょっと調べてみたら、エクセルでは正規表現が使えないみたいな内容があったのですが…何か必要なのでしょうか？？
最近マクロの本を読みながら、ちょっと出来るようになって喜んでいるのですが、まだまだ全然ダメですね。。。
奥が深いです。

回答No.3

noname#52504

#2です。
＞教えて頂いた B1:="" が上手く出来ませんでした。。。
＞数式を変えてみたらエラーになってしまって。
表現が紛らわしかったですね。すみません。
Ｃ列の数式自体ははそのまま変えません。
----------------------------------------------------
●元データが必ず数字から始まる場合

元のデータがA列1行目以下に入っているとします。
１．B1セルに下記の数式を入力
　=""
２．B1セルを下方にフィル
３．C1セルに下記の数式を入力（#2とまったく同じ式）
　=MID($A1,SUMPRODUCT(LEN($B1:B1))+1,MATCH(ISNUMBER(--B1),INDEX(ISNUMBER(--MID($A1&B1,SUMPRODUCT(LEN($B1:B1))+IF(ISNUMBER(--B1),INDEX(ROW($1:$20),),1),IF(ISNUMBER(--B1),1,INDEX(ROW($1:$20),)))),),0)-1)
４．C1セルを右方・下方にフィル
----------------------------------------------------
なお、B列ですが、
どちらにしろ見た目は空白なので、一見無駄っぽく思われますが、
計算に必要なので省けません、念のため。

お礼

ご丁寧に教えて頂き有難うございます。
出来ました (^0^)/
Ｂ列は別に品種を入力する必要があるので、ちょうど良い感じです。
今日、会社で上手く出来てニヤけてしまいました。
もうひとつお願いしてもいいでしょうか。
教えて頂いた数式をエクセルにコピーして使っていたので、
出来たらマクロに記録したいと思ってやってみたのですが、エラーになってしまいます。。。
もしよろしければ、VBAに入力する数式を教えて頂けませんでしょうか？

補足

同時に投稿した別の質問でお答え頂いた件、出来ました^^
そちらは締め切ってしまったので、こちらでお礼させて下さい。
Functionってスゴイですね。
こんなことも出来るなんて…奥が深いです。
今回質問した事によって勉強意欲がかなり沸いてきました。
有難うございました。

回答No.2

noname#52504

＞関数もしくはマクロ(VBA)で
ということなので、参考までに数式による解決を。

元のデータがA列1行目以下に入っているとします。

１．B列は何も入力しない
２．C1セルに下記の数式を入力

　=MID($A1,SUMPRODUCT(LEN($B1:B1))+1,MATCH(ISNUMBER(--B1),INDEX(ISNUMBER(--MID($A1&B1,SUMPRODUCT(LEN($B1:B1))+IF(ISNUMBER(--B1),INDEX(ROW($1:$20),),1),IF(ISNUMBER(--B1),1,INDEX(ROW($1:$20),)))),),0)-1)

３．C1セルを右方・下方にフィル

元のデータが文字から始まる場合はC列から分割して表示されます。
元のデータが数字から始まる場合はD列から分割して表示されます。

元のデータが必ず数字から始まるのであれば、
B1：=""　として下方にフィルすれば、C列から表示されます。

分割後の個々の数値・文字列が20文字を超える可能性がある場合には、
$20の部分を適当に増やしてください。
分割後の個々の数値・文字列が20文字より明らかに短い場合には、
$20の部分を適当に減らせば少し軽くなります。

Excel2003で動作確認済。

お礼

回答有難うございます。
出来ました！！感動しました！！
明日、早速仕事で使ってみたいと思います。
今から明日の仕事が楽しみです^^
でもこの数式、スゴイですね。
この数ヶ月、自分なりに考えてみて全然出来なかったのが納得です。

ちなみに、元データは必ず数字から始まるのですが
教えて頂いた B1:="" が上手く出来ませんでした。。。
数式を変えてみたらエラーになってしまって。
もしよろしければ、これも数式で教えて頂けると嬉しいです。

回答No.1

fumufumu_2006

1回で変換する方法もあると思いますが、正規表現に詳しくないので２回で行っています。
参照設定でMicrosoft VBScript Regular Expressionsを設定してください。

Sub test()
Dim s As String
s = "100AB3.4C"

Dim re As New RegExp
re.Global = True
re.Pattern = "([0-9.])([^0-9.])"
s = re.Replace(s, "$1"" ""$2")
re.Pattern = "([^0-9.])([0-9.])"
s = re.Replace(s, "$1"" ""$2")
s = """" & s & """"
Set re = Nothing

MsgBox s
End Sub

お礼

ご回答有難うございます。
教えて頂いた内容をVisual Basic Editorに入力してみたのですが、エラーになってしまいました。
Microsoft VBScript Regular Expressionsとは別物なのでしょうか？
それとも入力に間違いがあるのでしょうか？
そのままコピーして貼り付けてみたのですが。。。
ネットで調べてみたのですが良く分からなくて
プログラム以前の質問でスミマセン。