こんにちは、Ryoです。
Excelのコメント機能は便利ですが、その中のテキストデータを利用したい時にやや面倒なことになりますし、数値データのみを取り出したいこともあるかと思います。
少数のコメントなら良いですが、大量のコメントからデータを取り出すにはマニュアル作業では限界がありますのでVBAで処理してしまった方が楽ですので、今回はコメントからテキストを取得し、そのテキスト内にある数値データのみを抜き出す方法について書いてみます。
1.サンプル概要
サンプルとして画像に示すようにコメントがあり、その中には「温度」「湿度」「大気圧」データが書かれています。
このコメントからテキストデータ(文字列）を取得し、文字列内の空白や改行を削除してセルB11に表示すると共に、文字列内の「温度」「湿度」「大気圧」の数値データを抽出して、セルB13～B15にそれぞれ表示します。
2.サンプルコード
Sub Sample1()
'コメントからテキストを取得し、テキスト内の文字列から
'数値のみ取り出すサンプルコード
Dim Com_Txt, Txt, DataStr, Temp, Hum, Atm As String
Dim i, StrCnt As Integer
'セルB10のコメントを読み込む
Com_Txt = Cells(10, 2).Comment.Text
'Replaceを使用し取得したコメントテキストの改行、空白を削除する
Txt = Replace(Replace(Com_Txt, vbCrLf, ""), " ", "")
'サンプルとして表示用
Cells(11, 2) = Txt
'取得したテキストデータ文字数分繰り返し処理
For i = 1 To Len(Txt)
'先頭から1文字ずつ抜き取る
DataStr = Mid(Txt, i, 1)
'Like演算子を使い数字0～9、及び.を文字結合
If DataStr Like "[0-9.]" And StrCnt = 1 Then
Temp = Temp & DataStr
ElseIf DataStr Like "[0-9.]" And StrCnt = 2 Then
Hum = Hum & DataStr
ElseIf DataStr Like "[0-9.]" And StrCnt = 3 Then
Atm = Atm & DataStr
'テキストに含まれる"："を利用し、温度／湿度／大気圧の
'文字結合を切り替える為にStrCntを使用する
ElseIf DataStr = "：" Then StrCnt = StrCnt + 1
End If
Next i
'テキストから抽出したデータは文字列なので、Double型に変換する
Cells(13, 3) = CDbl(Temp)
Cells(14, 3) = CDbl(Hum)
Cells(15, 3) = CDbl(Atm)
End Sub
サンプルコード内に記述した内容について簡単ですが、以下に解説していきます。
◆コメント内容の取得
コメントのテキスト情報を取得するには、対象セルに対し.Comment.Textで取得できます。
対象セル.Comment.Text
サンプルではセルB10なので、変数Com_Txtに Cells(10, 2).Comment.Textの形で取得していますが、コメント内は改行されており空白も存在している為、このままの状態で利用しようとすると不都合が生じることもあります。
その為、次に説明する改行や文字列内空白の削除を行います。
◆文字列内の改行や空白の削除
文字列に対して、改行や空白の削除を行うにはReplace関数を使います。
改行を削除するには、
Replace(対象の文字列, vbCrLf, “”)
空白を削除するには、
Replace(対象の文字列, ” “, “”)
それぞれ、このように記述すればOKです。
サンプルでは両方処理する必要があるので、まとめて記述していますから、
Txt = Replace(Replace(Com_Txt, vbCrLf, “”), ” “, “”)としています。
使用しているReplace関数については、別記事でサンプルを交えて書いていますのでよろしければ参照してみてください。
◆文字列から数値データを取得する
この方法は他にも色々な手段があると思いますので、今回ご紹介する内容は一つの手段として参考にしてもらえればと思います。
先ずコメントから取得した文字列から改行や空白を削除し、その文字列の長さ（文字数）に対しFor～Nextで繰り返し処理を行います。
For i = 1 To Len(Txt)・・・Lenで文字列の長さを取得します。
次にMid関数を使い、1文字ずつ抜き出して処理を実行していきます。
DataStr = Mid(Txt, i, 1)
抜き取った文字に対し、必要なのは「温度」「湿度」「大気圧」の数値データなので、文字列の中から必要となる「数値」と「.」を判別、抽出を行い、文字列として連結させていきます。
抽出する上で使用しているのがLike演算子で、サンプルコード内に記述している通り、
If DataStr Like “[0-9.]” And StrCnt = 1 Then
Like[0-9.]とすることで、DataStr変数の文字が数値0～9、または . であればTrueを返してくれますから、それで判定しています。
Ifで判定しTrueとなった数値や.は文字列として結合していきます。
Temp = Temp & DataStr
元のテキストデータには抜き取る対象が3つありますので、文字列を結合していく上で条件分岐を行う必要があり、その切り替え判定用にStrCnt変数を設定しています。
これは文字列内にある”：”を抽出し、それが1回目=温度、2回目＝湿度、3回目＝大気圧となるので、コード内のIf文にStrCnt = 1 Thenなどで判定させています。
StrCnt = 1 ・・・Temp（温度）
StrCnt = 2 ・・・Hum（湿度）
StrCnt = 3 ・・・Atm（大気圧）
このループ処理が完了すると、それぞれ変数に数値データは抽出が出来ます。
但し、この時点では「文字列」としてのデータとなっていますので、数値変換をします。
CDbl(Temp)・・・倍精度浮動小数点数型（Double型）
CDbl関数を使うことでDouble型に変換出来ますので、セルに書き込むデータは「数値データ」となります。
◆参考
本サンプルは文字列内の「：」を利用して区分していますが、場合によっては利用できる文字が無いこともあるかと思います。その場合は以下の様に文字列を処理しながらカウントしていき、そのカウント数で区分させるという方法もあります。
≪例≫
For i = 1 To Len(Txt)
DataStr = Mid(Txt, i, 1)
If DataStr Like "[0-9.]" And StrCnt < 5 Then
Temp = Temp & DataStr
ElseIf DataStr Like "[0-9.]" And StrCnt < 10 Then
Hum = Hum & DataStr
ElseIf DataStr Like "[0-9.]" And StrCnt >= 10 Then
Atm = Atm & DataStr
Else
StrCnt = StrCnt + 1
End If
Next i
このように抽出対象外の文字列をカウントし、その数に対して分岐させるという方法もありますので一例としてご紹介しておきます。
3.まとめ
コメントの機能はとても便利な反面、あとで活用するのが大変だったりしますが今回ご紹介した内容で処理すれば、簡単に内容を取得出来ますので利用できる機会も多いのではないかと思います。
また、文字列から数値データのみを抽出したいケースは地味に多いと思いますのでLike演算子を使うことで対処出来ますから知っておくと便利です。今回のサンプルでは[.]を含んでいますが、整数の抽出であればLike[0-9]でOKです。
以上、コメントからテキストを取得し文字列から数値のみ取り出す方法についてでした！
今回の記事が何かの参考になれば幸いです。
Ryo
