こんばんは。
エクセルのセルで数字とアルファベットの文字列を分割する方法を教えて下さい。
100AB3.4C → "100" "AB" "3.4" "C"
間にスペースやカンマなど無く、文字数は異なります。
数字は小数点がある場合と無い場合があります。(上記の "100" "3.4" のように)
同じ列に同様のデータが数件~数千件あり、まとめて分割したいです。
関数もしくはマクロ(VBA)で可能であれば教えて下さい。
最近マクロの勉強を初めたのですが、方法が思いつきません。。。
宜しくお願いします。
No.6ベストアンサー
- 回答日時:
#2=#3です。
●ユーザー定義関数版
動作の概要
第1引数で指定した文字列を、数字と文字に分割し、
第2引数で指定した番号の文字列を返す
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/menu0 …
回答本当に有難うございます。
上手く出来ました。
サイトも教えて頂き有難うございます。
サイトは印刷して通勤途中に読んでます。
他にも色々やりたい事があるので、勉強がんばりたいと思います。
No.5
- 回答日時:
NO1さんも書かれているのですが、「ツール」→「参照設定」で
「Microsoft VBScript Regular Expressions 5.5」チェックしてください。
参考URL:http://codezine.jp/a/article/aid/1655.aspx
回答有難うございます。
正規表現、出来ました ^^
それで教えて頂いた内容でエラーにはならないのですが
上手く実行出来ませんでした。。。
例えば問題の内容"100AB3.4C"をA1セルに入力して、B1セルから分割内容が表示されるようにしたいのですが。
度々申し訳ございません。
もし良かったらもう少し教えて頂けると嬉しいです。
No.4
- 回答日時:
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
- 回答日時:
#2です。
>教えて頂いた B1:="" が上手く出来ませんでした。。。
>数式を変えてみたらエラーになってしまって。
表現が紛らわしかったですね。すみません。
C列の数式自体ははそのまま変えません。
----------------------------------------------------
●元データが必ず数字から始まる場合
元のデータがA列1行目以下に入っているとします。
1.B1セルに下記の数式を入力
=""
2.B1セルを下方にフィル
3.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)
4.C1セルを右方・下方にフィル
----------------------------------------------------
なお、B列ですが、
どちらにしろ見た目は空白なので、一見無駄っぽく思われますが、
計算に必要なので省けません、念のため。
この回答への補足
同時に投稿した別の質問でお答え頂いた件、出来ました^^
そちらは締め切ってしまったので、こちらでお礼させて下さい。
Functionってスゴイですね。
こんなことも出来るなんて…奥が深いです。
今回質問した事によって勉強意欲がかなり沸いてきました。
有難うございました。
ご丁寧に教えて頂き有難うございます。
出来ました (^0^)/
B列は別に品種を入力する必要があるので、ちょうど良い感じです。
今日、会社で上手く出来てニヤけてしまいました。
もうひとつお願いしてもいいでしょうか。
教えて頂いた数式をエクセルにコピーして使っていたので、
出来たらマクロに記録したいと思ってやってみたのですが、エラーになってしまいます。。。
もしよろしければ、VBAに入力する数式を教えて頂けませんでしょうか?
No.2
- 回答日時:
>関数もしくはマクロ(VBA)で
ということなので、参考までに数式による解決を。
元のデータがA列1行目以下に入っているとします。
1.B列は何も入力しない
2.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)
3.C1セルを右方・下方にフィル
元のデータが文字から始まる場合はC列から分割して表示されます。
元のデータが数字から始まる場合はD列から分割して表示されます。
元のデータが必ず数字から始まるのであれば、
B1:="" として下方にフィルすれば、C列から表示されます。
分割後の個々の数値・文字列が20文字を超える可能性がある場合には、
$20の部分を適当に増やしてください。
分割後の個々の数値・文字列が20文字より明らかに短い場合には、
$20の部分を適当に減らせば少し軽くなります。
Excel2003で動作確認済。
回答有難うございます。
出来ました!!感動しました!!
明日、早速仕事で使ってみたいと思います。
今から明日の仕事が楽しみです^^
でもこの数式、スゴイですね。
この数ヶ月、自分なりに考えてみて全然出来なかったのが納得です。
ちなみに、元データは必ず数字から始まるのですが
教えて頂いた B1:="" が上手く出来ませんでした。。。
数式を変えてみたらエラーになってしまって。
もしよろしければ、これも数式で教えて頂けると嬉しいです。
No.1
- 回答日時:
1回で変換する方法もあると思いますが、正規表現に詳しくないので2回で行っています。
参照設定で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とは別物なのでしょうか?
それとも入力に間違いがあるのでしょうか?
そのままコピーして貼り付けてみたのですが。。。
ネットで調べてみたのですが良く分からなくて
プログラム以前の質問でスミマセン。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) EXCEL VBAで教えてください。 1 2022/12/22 04:20
- Visual Basic(VBA) VBA B列にある前から10文字のみ表示 3 2023/08/07 11:24
- Excel(エクセル) Excel 文字列を結合するときに重複をなくしたい 関数・VBA 2 2022/12/12 10:40
- その他(プログラミング・Web制作) 2つのテキストファイルを比べて文字列を特定する方法を教えて下さい 5 2022/05/01 15:22
- Visual Basic(VBA) 特定の文字を簡単な操作で半角スペースに変換するか削除したい 2 2022/11/01 10:35
- その他(Microsoft Office) ある表(10桝程度)の中に数字が入っています。ダブっている数字を除く数字の合計数の計算方法 5 2023/02/15 11:33
- Excel(エクセル) エクセル関数の変わった使い方 3 2022/05/13 17:12
- Excel(エクセル) エクセルで2つの表を比較して、文字列が同じだが、その行のある値が違うものを抽出したい 1 2022/10/06 21:48
- Excel(エクセル) エクセルの条件付き書式で*を使いたい 4 2022/05/13 16:49
- Excel(エクセル) 【エクセル関数】アルファベットで以上だったらカウントする 4 2022/07/09 14:07
このQ&Aを見た人はこんなQ&Aも見ています
-
「どうして捨てられないの?」前妻の物を捨てられない男性の心理って?
前妻の物を捨てられない理由に加え、捨てるための手段はあるのかを専門家に聞いてみた!
-
数値とアルファベットに分ける(エクセル)
Excel(エクセル)
-
文字列から英数字のみを抽出する関数
Excel(エクセル)
-
Excelの1つのセルに入力されたアルファベットと数字を分けるには?
Excel(エクセル)
-
-
4
エクセル 同一セル内の数値と文字を分ける関数
Excel(エクセル)
-
5
Excel セル内の文字と数字をわけるには??
Excel(エクセル)
-
6
エクセルでアルファベットと日本語の文字列を分割する方法
Excel(エクセル)
-
7
split関数で区切り文字がない場合
Visual Basic(VBA)
-
8
VBAにて 文字と数字が混在してるデータの並び替え
Excel(エクセル)
-
9
【Excel VBA】CSV取込時、数字の先頭の0を消えないようにするには?
Excel(エクセル)
-
10
worksheetFunctionクラスのVlookupプロパティを取得できません エラーへの対応
Visual Basic(VBA)
-
11
フォントの大きさ
Visual Basic(VBA)
-
12
VBAで既に開いている別アプリケーションのオブジェクトを得る
Visual Basic(VBA)
-
13
VBAでエクセルシートを更新(リフレッシュ)する方法を教えて下さい。
Excel(エクセル)
-
14
EXCEL VBAで全選択範囲の解除
Excel(エクセル)
-
15
EXCEL VBA セルに既に入力されている文字に文字を追加する
Excel(エクセル)
-
16
VBAでの一時停止と再開の方法
その他(プログラミング・Web制作)
-
17
セルにカーソルを合わせると文字を表示させるVBA
Excel(エクセル)
-
18
UserForm1.Showでエラーになります。
工学
-
19
エクセルVBAのIF文の否定の複数条件
Excel(エクセル)
-
20
エクセル VBA 小数点を含む数字抽出について
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのマクロについて教え...
-
ExcelVBA修正のお願い
-
エクセルのマクロについて教え...
-
VBAコードについて教えてくださ...
-
[vb.net] 起動したFrom2を閉じ...
-
エクセルのVBAコードについて教...
-
[VB.net] ボタン(Flat)のEnable...
-
エクセルのマクロについて教え...
-
エクセルのマクロについて教え...
-
VBA 別ブックからコピペしたい...
-
重複確認
-
Excel(M365) Vlookup/セル反転(...
-
エクセルのVBAコードについて教...
-
コードを直していただきたいで...
-
{ CONTROL Forms.Label.1}が...
-
マクロの記録を使用したマクロ...
-
Excel VBA 定義されたプロージ...
-
VBA 複数の各シートに行を追加...
-
配列のペースト出力結果の書式...
-
VBA実行後に元のセルに戻りたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBAなくなるの?
-
VBAでCOPYを繰り返すと、処理が...
-
vba 削除
-
プログラミング
-
Excelのマクロについて教えてく...
-
Excelのマクロについて教えてく...
-
エクセルのVBAコードについて教...
-
久しぶりのプログラミング
-
ユーザーフォームに別シートか...
-
エクセルVBAコードで教えて下さ...
-
VBA 別ブックからコピペしたい...
-
ExcelのVBAコードについて教え...
-
VBAコードについて教えてくださ...
-
vba アクティブシートの名前変...
-
Excelのマクロについて教えてく...
-
エクセルVBA
-
Geogebraの操作方法について
-
マクロの記録を使用したマクロ...
-
Excel(M365) Vlookup/セル反転(...
-
Excel 範囲指定スクショについ...
おすすめ情報