エクセル97と2000VBAでの区切り方法

質問

下記データがひとつのセルに収まっているとします。

2 DE1B-24 H1 H2 H3 HIROSE 24PIN

普通にエクセルでデータのスペース区切りを使うと、スペースごとに区切られますが、私のやりたいことは違って「H*(*は数値）」と並んでる箇所はスペースで区切らず、ひとつのセルにまとめておきたいのです。（その他はスペースで普通に区切ります）

尚、「H*」はアルファベットと数値の組み合わせのデータで（これは決まっています）で、「CN*」や「FIN*」とかあったりします。また*は一桁とは限りません。データも3つとは限らず1つの場合や3つ以上の場合があります。

説明下手ですが理解していただいた方々、マクロやVBAを使ってどうにかならないでしょうか？

回答No.2

nishi6 · ベストアンサー

少々長くなってしまいました。なかなかおもしろい問題？でした。
分解したいセルを選択して、下記マクロを実行します。
分解したいセルの右隣から横に分解するはずです。
　　　（当方Excel2000です。Excel97でも問題ないでしょう）

Const Alp1 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Const Alp2 = "abcdefghijklmnopqrstuvwxyz"
Const NUM1 = "0123456789"

Sub Bunkatu()
　　Dim rg As Range　　　　　'// セル
　　Dim str As String　　　　'// 対象の文字列
　　Dim elm As String　　　　'// 個々の文字列
　　Dim outElm As String　　 '// 出力する文字列
　　Dim pot As Integer　　　 '// 半角スペースの位置
　　Dim col As Integer　　　 '// 行、列カウンタ
　　Dim chkFlg As Boolean　　'// 分割するかどうかのチェックフラグ

　　For Each rg In Selection
　　　　str = rg.Text & " @@@"　 '// 最後の文字の処理のため最後に『番兵』をつける
　　　　col = 0: outElm = ""

　　　　pot = InStr(str, " ")
　　　　While pot > 0
　　　　　　elm = Left(str, pot)

　　　　　　'// 分割するか判定する
　　　　　　If chkBunkatu(elm) = True Then
　　　　　　　　If outElm <> "" Then
　　　　　　　　　　col = col + 1: rg.Offset(0, col) = outElm
　　　　　　　　End If
　　　　　　　　col = col + 1: rg.Offset(0, col) = elm
　　　　　　　　outElm = ""
　　　　　　Else
　　　　　　　　outElm = outElm & elm
　　　　　　End If

　　　　　　str = Right(str, Len(str) - pot)
　　　　　　pot = InStr(str, " ")
　　　　Wend
　　Next
End Sub

'// 文字が、英字→数字→の構成なら『TRUE』を返す関数
Function chkBunkatu(ByVal elm As String) As Boolean
　　Dim L As Integer　　　　 '// 文字カウンタ
　　Dim pot As Integer　　　 '// 文字を調べる位置
　　Dim AlpChk As Boolean　　'// 英字のチェックフラグ
　　Dim NumChk As Boolean　　'// 数字のチェックフラグ
　　Dim chk As Boolean　　　 '// 条件に合うかのチェック

　　elm = Trim(elm)
　　'// 左から英字だけか調べる
　　pot = InStr(Alp1 & Alp2, Left(elm, 1))
　　While Len(elm) > 0 And pot > 0
　　　　AlpChk = True
　　　　elm = Right(elm, Len(elm) - 1)
　　　　pot = InStr(Alp1 & Alp2, Left(elm, 1))
　　Wend

　　'// 右から数字だけか調べる
　　pot = InStr(NUM1, Right(elm, 1))
　　While Len(elm) > 0 And pot > 0
　　　　NumChk = True
　　　　elm = Left(elm, Len(elm) - 1)
　　　　pot = InStr(NUM1, Right(elm, 1))
　　Wend

　　chkBunkatu = True
　　If elm = "" And AlpChk = True And NumChk = True Then
　　　　chkBunkatu = False
　　End If
End Function

お礼

どうもありがとうございます。
処理に時間がかかってしまうのは仕方の無いことなんでしょうか。
参考に使用させていただきますm(__)m

回答No.1

imogasi

実例でももう少し沢山挙げないと質問の意味が取れません。
＞普通にエクセルでデータのスペース区切りを使うと、スペースごとに区切られますが
そうでもないです。カンマで区切るのが一番代表的です。
＞「H*(*は数値）」と並んでる箇所はスペースで区切らず
－＞しかし実例ではＨ＊のあとがスペースが入っている例を上げておられるが、実際はどうなのでしょう。
H1H2H3HIROSEとなっているのでしょうか。
＞その他はスペースで普通に区切ります
普通にスペースで区切ると、どんな困った（したいような状態と反する）状態になるのですか。
＞「CN*」や「FIN*」とかあったりします
「CN*」や「FIN*」は「Ｆ*」と同じような接頭辞扱いですか。別セルにしたいのでしょうか。
「Ｆ*」「CN*」や「FIN*」のほかにどんな記号が、何種類ぐらいあるのでしょうか。
○プログラムを組める前提は、文章できっちり表現できることです。文章に表現できても、プログラムにするには難しいことも多いです。
他人にやらせる場合はなおさらです。
（１）スペースで一旦区切る。－＞各々を各セルに分散する。
（２）分散したセルの中身を見て、「Ｆ」、「CN」、「FIN」で始まるストリングに注目する。
（３）切り出す終りは、続く数字の終りとする。
（４）切り出せたら、「それより前」「切りだし部分」「その後の部分」と３セルに分散し、後続部分セルは右列へずらす。
こう言うタイプの文字列を処理するプログラムは、力ずくのスッキリしないプログラムになります。

補足

説明不足ですみません。補足して行きます。
まず、データの例は
2▲DE1B-24▲H1▲H2▲H3▲HIROSE▲24PIN
となっています（▲はスペース）。スペースは全角の時もあれば半角の時もあり、何個もスペースが入ってる時がありますが、必ずスペースでデータ分けされています（カンマ等ではデータ分けされていません）。

VBAでやりたい事はスペースで区切った時に
H1▲H2▲H3の部分はひとつのセルにまとめたいと言うことです。

＞そうでもないです。カンマで区切るのが一番代表的です。
→今回私が挙げたデータはスペースでデータ分けされているのでスペースで区切ろうと思っています。

＞しかし実例ではＨ＊のあとがスペースが入っている例を上げておられるが、実際はどうなのでしょう。
→「H*」のデータが並んでいるところもスペースでデータ分けされていますが、そこだけ区切りたくないと言う意味です。

＞普通にスペースで区切ると、どんな困った（したいような状態と反する）状態になるのですか。
→スペースで区切ると同じセルにまとめておきたいデータ（例で挙げたH1▲H2▲H3部分）まで区切られてしまいます。

＞「CN*」や「FIN*」は「Ｆ*」と同じような接頭辞扱いですか。別セルにしたいのでしょうか。
→これらのデータは同じセルにしたいです。

＞「Ｆ*」「CN*」や「FIN*」のほかにどんな記号が、何種類ぐらいあるのでしょうか。
→区切った時にひとつのセルにまとめておきたいデータは必ず「アルファベット+数値」の組み合わせになっていますが、アルファベットの組み合わせは無限大です。