マクロである文字以降の文字列を取得するには？

質問

２枚のシートの一部ずつを条件に合わせ取り入れ新シートを作るマクロを作っています。
そこで１つ問題な列があります。
D列の各セルには「,」で区切られて２つのデータが入っています。

123 , 456
55 , 67
8 , 1
678 , 9　　　　　　の様な形です。

「,」の両側にはスペースが入っています。
このうちの「,」の右側の数値を変数に入れたいんですが、文字数が行によって違います。
右側がスペースを含めて３文字に決まっているなら、
　　変数 =　Right(Cells(i, 4), 3)
の様な感じになりますが、一桁から三桁まであります。
こういう場合、「,」の右側を取得する関数とか有るんでしょうか？
それとも他の方法を取るんでしょうか？

回答No.2

Wendy02 · ベストアンサー

こんばんは。

＞「,」の右側を取得する関数とか有るんでしょうか？
Excel 2000以上では、Split 関数もありますが、他には、InStr 関数を使うことが出来ます。ワークシート関数のFind関数等に相当します。VBAのMid関数は、最後まで取る時は、Mid(文字列,スタート,文字数）の文字数を省略できます。これは、数値のみになります。

Sub Sample2()
Dim c As Range
Dim Rnum As Long
For Each c In Range("D1", Range("D65536").End(xlUp)).Cells
　 If Not IsEmpty(c) Then
　　 On Error Resume Next
　 '右側の数値
　　 Rnum = CLng(Mid$(c.Value, InStr(c.Value, ",") + 1))
　　 On Error GoTo 0
　 '処理例・右側の数字
　　 c.Offset(, 1).Value = Rnum
　　 Rnum = 0 'エラーが発生した時に、クリアするため
　 End If
Next
End Sub

ちなみに、左側の場合は、以下のようなコードになります。
Lnum = CLng(Mid$(c.Value, 1, InStr(c.Value, ",") - 1))

お礼

InStrで文字を指定し、Midと組み合わせても良いんですね。
教えて頂いたので、旨くいきました。
丁寧なマクロ有り難うございました。

回答No.3

imogasi

下記のどれか
（１）マクロを使わずともデータ-区切り位置-・・・の操作でできる。
（２）VBAでは（１）にあたるのはSplit関数です
（３）Instr関数でカンマの位置を知り、Mid関数で分離し、TRIM関数で余分なスペースを取り除く

お礼

以前、imogasiさんにお答えを頂いた事があるんですが、すでにお礼の欄を書いて締め切る時だったので、失礼しました。
これからもよろしくお願いします。

回答No.1

bin-chan

VBAの関数「Split」を利用します。
文字列を指定文字（デフォルトはカンマ）で配列に変換するので
ご希望の「カンマの右」は(1)の要素です。(0)はカンマの左。

標準モジュールの追加を行い、下記を貼り付けてください。
シート上でセルＤ２を分離し、Ｅ２に表示するには
セルＥ２に式「=分離(D2)」を設定してください。
ただし、戻り値を文字型にしてあるので、適宜数値へ変換の判断を加えてください。
変数名は説明を兼ねて漢字にしていますがそのままで動作します。

Function 分離(文字列 As String) As String

Dim 文字配列() As String
Dim 配列件数 As Long

文字配列() = Split(文字列, ",")
nArrey = UBound(文字配列())
If nArrey > 0 Then
分離 = Trim(文字配列(1))
Else
分離 = ""
End If

End Function

お礼

Splitを使えば良いんですね。、有り難うございます。

これは、そのままで使うと変数に文字列を入れると言うより、もう一つの列に分離したのを入れるユーザー関数ですよね？
エラーが出たんですが、nArreyが引っ掛かってると思うんで(Option Explicit)、さらに試してみます。UBoundなど難しい関数が出てるんでもうチョッと勉強してみます。