Excel VBAで区切り文字前後の文字列を抜き出す方法
- Excel VBAを使用して、区切り文字の前後にある文字列を抜き出す方法について説明します。
- A列には時間と文字列の組み合わせがあります。時間と文字列の順番が異なる場合や、時間が括弧で括られている場合もあります。これらの統一されていないフォーマットを一つのフォーマットに統一したい場合には、Excel VBAを使用することで簡単に実現することができます。
- 具体的な手順は以下の通りです。 1. 指定した区切り文字の前後にある文字列を抜き出すためのVBAコードを作成します。 2. A列の各セルに対してVBAコードを適用し、区切り文字前後の文字列をB列に書き出します。 3. 統一されたフォーマットの文字列がB列に書き出されます。
- ベストアンサー
VBA - 区切り文字前後で抜き出す
Excel(VBA)で 区切り文字の前後で抜き出す(フォーマット統一) A列に 時間(00:00又は00:00:00)と文字列の組み合わせが複数あります。 順番が以下の2パターン 時間 文字列 文字列 時間 時間は、半角のカッコ"()"で括られている場合もあります。 両者は、半角スペース(1個又は数個) 又はそれ以外の区切り文字列(-,|)で区切られています。 (半角スペースと区切り文字の組み合わせも有り) これらの統一されていないフォーマットを一つのフォーマットに統一したいと思います。 統一フォーマット形式は 区切り文字として半角スペース(1個)で、下記を想定 hh:mm:ss mojiretu (hh:mm:ssは、例えば 00:05:15) B列に変換されたフォーマットを書き出す ------------------------------------------------------ 元ターゲットは以下のフォーマットが想定されます。 0:00 second hand news 00:00 00 - It's Not All Truth #1. 00:00 - Heldom - Hávamál 00:00 - Drifts 00:00:00 Dream of Arrakis (00:00) Kontora 01. Sun Killer 00:00 1. A World Away | 0:00 1. Imperial - 00:00:00 Silent universe 00:00 01. The Tower [Theme from "The Work"] (00:00) Dotugo - (00:00:00) StarBath Jam 1 00:00 Burning 00:00 参考画像 https://imgur.com/CcO1moz ---------------------------------------------- 以下で区切り文字(@)の前後で文字列をそれぞれ書き出せますが 私のやりたいことは複雑なのでどのようにコードを変換したら良いでしょうか ? DimmyStrAsString DimfindStrAsString DimfindNoAsInteger myStr=Range("A1") findStr="@" findNo=InStr(myStr,findStr) Range("B1")=Left(myStr,findNo-1) '@より前の文字列' Range("D1")=Right(myStr,Len(myStr)-findNo) '@より後の文字列'
- NuboChan
- お礼率65% (1295/1974)
- Excel(エクセル)
- 回答数5
- ありがとう数2
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
> 表示がおかしい所があります。 2か所ある ' Cells(i, "B").Value = mTime & " " & mStr を削除して Nextの前に If Len(mTime) - Len(Replace(mTime, ":", "")) = 1 Then mTime = "00:" & mTime End If Cells(i, "B").Value = Format(mTime, "hh:mm:ss") & " " & mStr を追加してください。 0:00 second hand news が 00:0:00 second hand news になるので Format(mTime, "hh:mm:ss") にしてます。
その他の回答 (4)
- kkkkkm
- ベストアンサー率65% (1620/2460)
> 又はそれ以外の区切り文字列(-,|)で区切られています。 その場合でも時間の後または前に半角スペースがあるので(無ければ駄目ですが) がむしゃらに頭を使わすに力技で ただ 00:00 00 - It's Not All Truth 最後の00の前に「:」が無いのは #1. 00:00 - Heldom - Hávamál 変更後も 「- Heldom - Hávamál」 で頭に「-」があるのは の2点が不明だったのと 00:00の前に00を付けるのか後ろにつけるのか不明だったので mTimeの前か後ろに追加してください。 Sub Test() Dim buf As Variant Dim i As Long, temp As String Dim mTime As String, mStr As String For i = 1 To 14 buf = Split(Trim(Cells(i, "A").Value), " ") If InStr(buf(0), ".") > 0 Then temp = Trim(Replace(Cells(i, "A"), buf(0), "")) Else temp = Cells(i, "A").Value End If buf = Split(Trim(temp), " ") If InStr(buf(0), ":") > 0 Then If InStr(buf(0), "(") > 0 Then mTime = Mid(buf(0), InStr(buf(0), "(") + 1, InStr(buf(0), ")") - InStr(buf(0), "(") - 1) Else mTime = buf(0) End If mStr = Trim(Replace(temp, buf(0), "")) If Left(mStr, 1) = "-" Then mStr = Trim(Right(mStr, Len(mStr) - 1)) End If Cells(i, "B").Value = mTime & " " & mStr Else If InStr(buf(UBound(buf)), "(") > 0 Then mTime = Mid(buf(UBound(buf)), InStr(buf(UBound(buf)), "(") + 1, InStr(buf(UBound(buf)), ")") - InStr(buf(UBound(buf)), "(") - 1) Else mTime = buf(UBound(buf)) End If mStr = Trim(Replace(temp, buf(UBound(buf)), "")) If Right(mStr, 1) = "-" Or Right(mStr, 1) = "|" Then mStr = Trim(Left(mStr, Len(mStr) - 1)) End If Cells(i, "B").Value = mTime & " " & mStr End If Next End Sub
お礼
kkkkkmさん、今回もお世話になります。 記載ミスや説明不足が多くお世話をお掛けします。 >00:00 00 - It's Not All Truth >最後の00の前に「:」が無いのは 「:」が無いのはミス記入です。 00:00:00 - It's Not All Truth が正解です。 >変更後も >「- Heldom - Hávamál」 >で頭に「-」があるのは 頭の「-」は、必要ありません。 成形後は、下記になります。 00:00:00 Heldom - Hávamál >00:00の前に00を付けるのか後ろにつけるのか不明だったので 00:00 は、mm:ss相当で hh:mm:ssに成形するので「00」は、前に付加します。 >mTimeの前か後ろに追加してください。 2か所あるmTimeを下記のようにしましたが、 Cells(i, "B").Value = "00:" & mTime & " " & mStr 表示がおかしい所があります。 添付画像の□で囲った箇所に注目ください。 (添付画像は、実際に即した数値に変更しました。) コードの「00:」の記載方法が変でしょうか ?
補足
添付ミスで 添付画像がありませんでした。 下記参照ください。 https://imgur.com/DQajzq6
- SI299792
- ベストアンサー率48% (715/1480)
グーグルスプレッドシートにはRegexExtractという便利な関数があります。 便利なのでユーザー定義関数で作りました。 使い方はグーグルスプレッドシートと全く同じです。従って、グーグルスプレッドシートならこの関数だけでできます。 B1: =RegexExtract(A1,"\d{1,2}(:\d{1,2}){1,2}") 下へコピペ。 Option Explicit ' Function RegexExtract(Expression As String, Pattern As String) As String Dim RegExp As Object ' Set RegExp = CreateObject("VBScript.RegExp") RegExp.Pattern = Pattern RegexExtract = RegExp.Execute(Expression)(0) End Function
- tanakanono
- ベストアンサー率24% (134/553)
正規表現で抽出できますよ。検索すれば出てきますのでここで詳細に書くより検索したほうが見やすいし理解しやすいと思いますので省略します。 例えば、 https://masalib.hatenablog.com/entry/2013/12/03/094907 https://excel-ubara.com/excelvba4/EXCEL232.html http://excel-vba.jpn.org/section/12/StringOperation-VBA
関連するQ&A
- 時間部分を括弧で囲う
文字列中に時間相当の数値がある場合 時間のみ括弧で囲うワークシート関数を教えて下さい。 例えば A2: Green ROM 12:34 B2: Green ROM (12:34) A2: soft Ball 05:12 B2: soft Ball (05:12) 時間区切りは、半角(:)又は全角(:)がありえます。 スペースも、半角、全角 がありえます。 見栄えを整えるのにスペースが連続することもあります。 例:soft Ball 0512 時間は、以下の形式を考慮します。 hh:mm:ss h:mm:ss mm:ss m:ss
- ベストアンサー
- Excel(エクセル)
- データファイルの入力方法
データファイルの入力方法についての質問です。 フォーマットは以下↓の通り、1,2列目が日付,時間となっています。 (表記の都合上、半角スペースを_で置換しています。) 各行それぞれ3列目から最後の列までの和を計算したいと思っています。 問題点は以下の3点です ・1,2列目をとばしたい ・行によって列の数が異なる ・列の区切りのスペースの数がバラバラ 上の場合以外(つまりデータのみで列の数がそろっていて区切りも統一されている) のファイルでしたらfscanfを使ってできます。 ご助言をよろしくお願いします。 2006/06/10____10:10:10____320.25____351.2____561.00___2646.00 2006/06/10____10:10:11____320.000___310.00___561.00 2006/06/10____10:10:12____320.000___310.00___561.00___2646.0 2006/06/10____10:10:13____320.000___310.00___561.0____2646.00 2006/06/10____10:10:14____320.0_____310.00___561.00___260.0 2006/06/10____10:10:15____310.00____561.00___2646.00 2006/06/10____10:10:16____320.00____310.00___561.00___2646.00 2006/06/10____10:10:17____320.000___310.00___561.0____2646.0 2006/06/10____10:10:18____310.0_____561.00___2646.00 2006/06/10____10:10:19____320.000___310.00___561.00___2646.0 2006/06/10____10:10:20____320.000___310.00___561.00___2646.00 _
- ベストアンサー
- C・C++・C#
- splitを使ってスペース位置で文字列を区切りたい
splitを使ってスペース位置で文字列を区切りたいのですが、 String[] word = str.split(" ",0); //半角空白 のように書くと、全角スペースは区切りとしてみなされず、 String[] word = str.split(" ",0); //全角空白 のように書くと、半角スペースは区切りとしてみなしてくれません。 全角スペースでも半角スペースでも、スペース位置で文字列を区切りたいのですが、どのように書けばよいのでしょうか?
- ベストアンサー
- Java
- VBAで正規表現
Excel2000-VBAにてマクロ作成中です。 以下のような処理を正規表現を使用して一発で実行させたいのですが、 正規表現に指定するパターンがわかりません・・・。 どなたがご存知でしたら教えて下さい。 やりたいこと:ある文字列中の中のスペース(半角全角両方)を半角スペースに変換し、 さらに連続している半角スペースを1つに置換したい。 スペースを半角に統一することは、Replace関数で可能かと思っています。
- ベストアンサー
- Visual Basic
- Excelでカンマ区切りの文字列をセルに分割する?
住所録をExcelにコピーしたら、 カンマ区切りに一つのセルに入ってしまいました。 ◇カンマごとに一つのセルに分割して文字列を分ける。 ◇氏と名の間は半角スペースでカンマでは有りませんが、 二つのセルに分ける。 上記について分ける関数を教えてください。
- ベストアンサー
- Windows 7
- 【VBA】 文字列の中から指定の文字列を取り出す
VBAで文字列から指定の文字のn番目からn+1番目までの文字列を取り出すことは可能でしょうか? A1セルに下記の文字列があった場合、「1番目の半角スペースから2番目の半角スペースまでの文字列」を取り出したいのです。 5 53 00 8R この場合、53を取り出したいというわけです。 また、可能でしたら「最後の半角スペースから文字列の最後まで」を取り出す方法も教えていただけるとありがたいです。 この場合は8Rとなります。 InStr関数を使えばできるかもと思ったのですが、できそうなものが思い浮かびません。 どなたか教えていただけませんでしょうか。 よろしくお願いいたしますm(_ _)m
- ベストアンサー
- その他MS Office製品
- 文字列の部分読み込み
C言語によるプログラムを勉強中なのですが、分からないことがあるので質問させてください。 「fgets」を用いてファイルから一行分の文字列を読み込んだのですが、この文字列の○文字目から×文字目を読み込む関数などはあるのでしょうか? もし無ければ、どのようにしたら読み込めるのでしょうか? 読み込んだ文字列は □□□□1234□□□5678□□abcd のようなもので、□は半角のスペースです。 区切り文字などが無く、先頭の半角スペース部分に文字が入る場合もあるので、○文字目から×文字目のように指定したいと思っています。 初心者で申し訳ありませんが、よろしくお願いいたします。
- ベストアンサー
- C・C++・C#
- VBA 1セルからスペース区切り 文字列の切り取り
Fa0/22_______________________disabled_____1____________auto___auto_10/100BaseTX Fa0/23_______________________notconnect___1____________full___100__10/100BaseTX Fa0/24_______________________connected____1__________a-full__a-100_10/100BaseTX 「_」アンダースコアは半角スペースに読み替えてください。 上記のような文字列を各セルに貼り付けています。上記の例では3行です。 この中の半角スペース以外の文字列を切り出して他のセルにコピーしたいのです。 Fa0/22 disabled 1 auto auto と結果的に5つに分解したいのです。 切り出す文字数は上記の通り様々でして、半角スペース数も常に固定という訳では ありません。 正規表現を使うのだと思いますが具体的にどう検索すればよいのか分かりません。 ご教示のほど、よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- Excel2010 VBAでスペース区切り
はじめて質問させて頂きます。 VBA初心者ですが、宜しくお願いします。 CSVデータをエクセルにコピペしてから処理することを考えています。 CSVのデータはエクセルで開くと以下の様に入力されています。 | A列 | B列 | C列 | 1行目| 日付 時刻 |データ1|データ2| 2行目|2014/5/14 13:00:01| ○ | △ | 3行目|2014/5/14 13:00:14| ○ | △ | 時刻は24h制で入力されています。 A列の日付と時刻の間にはスペースがあり、これをVBAでスペース区切りをしたいのです。 B列に列を追加し、スペース区切りの処理をしようと思い、自動記録すると以下のようになりました。 Sub Macro1() Columns("B:B").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=True, Semicolon _ :=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array( _ Array(1, 5), Array(2, 1)), TrailingMinusNumbers:=True End Sub ところが、このマクロを実行すると「コピーまたは移動先のセルの内容を置き換えますか?」のメッセージが表示されます。(記録する際には表示されませんでした。) 原因を探ったところ、自動記録のマクロを実行すると時刻が12時間制に変更されB列に、 C列にAM/PMが書き込まれます。(A列の日付は問題なく処理されます。) B列に24時間制の時刻が書き込まれるようにしたいのですが、 何か不足してるとことがあるのでしょうか? 方法を教えて下さい。 お手数ですが、宜しくお願いします。
- ベストアンサー
- Visual Basic
- エクセルVBAの文字列操作について2
エクセルVBAの文字列操作について2 以前、こちらでご教授いただいた以下のような文字列操作方法があります。 この方法ですと例えば[1-3]から3をひいた際に"1-2"と表示されますが 今回は連続する数字が2つの場合は1,2と表示させ3つ以上の場合は-でつないで表示させたいと思います。 一週間ほど考えたのですが解決できませんでした。 どなたかご協力お願いいたします。 質問内容 例えば、[1-10,15-20,22-38]と入っているセルがあるとします。 このセルに数を足したり引いたりしたいのです。 例えば、このセルから”5”を引いて[1-4,6-10,15-20,22-38]と表示したり、 "21"を足して[1-10,15-38]と表示したい。 いただいたご回答 A1 セル に「1-10,12,15-20,22-38」と入力されているとして、別のセルに =NUMORDER(A1,-5) と入力すると「1-4,6-10,12,15-20,22-38」と表示し =NUMORDER(A1,21) と入力すると「1-10,12,15-38」と表示します。 1つ目の引数には「セル番地」または「文字列」を、2つ目の引数には「1 ~ 99 までの整数」をお入れください。 Function NUMORDER(myStr As Variant, num As Integer) As String Dim i As Long Dim j As Double Dim myNum As Variant '文字列中の スペース を削除 myStr = Replace(myStr, " ", "") '文字列の前後に「0」・「100」を挿入 Select Case Left(myStr, 2) Case "1,", "1-" myStr = myStr & ",100" Case Else myStr = "0," & myStr & ",100" End Select '文字列を カンマ で分割し、ハイフン の区間の数字を補完する myStr = Split(myStr, ",") For i = 0 To UBound(myStr) If InStr(myStr(i), "-") > 0 Then myNum = Split(myStr(i), "-") myStr(i) = "" For j = myNum(0) To myNum(1) myStr(i) = myStr(i) & " " & j Next myStr(i) = Trim(myStr(i)) End If Next '欠番に「●」を入れ、「数を足したり引いたり」する myStr = Split(Join(myStr)) For i = 0 To UBound(myStr) - 1 myStr(i) = myStr(i) & Application.WorksheetFunction.Rept(" ●", myStr(i + 1) - myStr(i) - 1) Next myStr = Split(Join(myStr)) If num > 0 Then myStr(num - myStr(0)) = num Else myStr(-num - myStr(0)) = "●" End If '前後に挿入した「0」・「100」を削除 myStr = Replace(Join(myStr), " 100", "") If Left(myStr, 2) = "0 " Then myStr = Right(myStr, Len(myStr) - 2) '連続数字を ハイフン で繋ぐ myStr = Split(myStr, "●") For i = 0 To UBound(myStr) If myStr(i) <> " " Then myNum = Split(Trim(myStr(i))) If UBound(myNum) > 0 Then myStr(i) = myNum(0) & "-" & myNum(UBound(myNum)) End If End If Next 'カンマ で文字列に分割する myStr = Application.Trim(Join(myStr)) NUMORDER = Replace(myStr, " ", ",") End Function
- 締切済み
- オフィス系ソフト
お礼
kkkkkmさん、今回もおかげさまで 訂正されたコードで上手く処理できました。 お礼申し上げます。