- ベストアンサー
エクセル2010で規則性のある文字列の取り出し
cj_moverの回答
- cj_mover
- ベストアンサー率76% (292/381)
#7、cjです。 全体を読み返して気が付いたことがあるので、追加自己レスです。 マクロは2例とも差し替えになります。 > マッチした位置情報に意味は ... 質問文からは読み取れませんでしたが、 元データと同じ行位置に結果を出力したい、ということのようなので、 書き直しました。 事前に想定はしていませんでしたが、少しの修正で済みました。 ' ' ==========簡易簡潔版============================== Sub Re8819211a改() Dim c As Range Dim sTmp As String Dim nPos As Long Dim cn As Long Application.ScreenUpdating = False For Each c In Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row) ' セル範囲を総当たりループ sTmp = c ' セル値を文字列変数に ' nPos = 0 cn = 0 Do ' Doループ("B"、"G"が見つからなくなるまで文字列の桁位置を検索) nPos = NaturalMin(InStr(nPos + 1, sTmp, "B"), InStr(nPos + 1, sTmp, "G")) ' 関数解説参照 If nPos Then ' "B"、"G"が見つかったならば If Mid$(sTmp, nPos) Like "[BG]####*" Then ' "B"、"G"から始まり数字が4桁続く文字列ならば cn = cn + 1 ' ヒット数送り Cells(c.Row, "K")(1, cn) = Mid$(sTmp, nPos, 5) ' マッチした文字列を出力 End If Else ' "B"、"G"が見つからないなら Exit Do ' Doループを抜け次のセルへ End If Loop Next Application.ScreenUpdating = True End Sub ' ' /// 2つの引数の内 最少の自然数(0以外の最少数)を返す。 ' ' /// 2つの引数の両方が0ならば0を返す。 Private Function NaturalMin(ByVal n1 As Long, ByVal n2 As Long) As Long NaturalMin = Application.Min(n1, n2) If NaturalMin = 0 Then NaturalMin = Application.Max(n1, n2) End Function ' ' ================================================== 比較的処理が速い方の版については、記述内容としては寧ろ簡素になります。 ただ、 ひとつのセルに複数マッチした場合に、 単純にそれらを(区切り文字なしで)連結したものを出力するのが基本仕様です。 なので、複数列に分けて出力できるように、オマケを10行書き足しました。 ひとつのセルに複数マッチすることは無い、と担保されていた場合でも、 オマケの記述以外は全く同じものになりますから、余計なコストは掛かっていません。 逆に複数マッチしても最初にマッチしたものだけを返すなんて条件も想像できますが、 少しの工夫で済みますし、後から処理するのでも難しくないですから、ここには書きません。 重複の削除については、元データと同じ行位置に結果を出力するならば不要でしょうし、 今回の書換えによって、ついでに処理するようには書けないので、ここには書きません。 ' ' ==========一連文字列・正規表現・貼り付け版 ======= Sub Re8819211c改() Dim oData As Object ' As MSForms.DataObject Dim oRE As Object ' As VBScript_RegExp_55.RegExp (正規表現) Dim sSrc As String Application.ScreenUpdating = False Set oData = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' New DataObject Range("B:B").Copy ' セル範囲(任意の列単位:複数列可)のコピーデータをクリップボードに oData.GetFromClipboard ' クリップボードのコピーデータをDataObjectに sSrc = oData.GetText ' (tab区切り)cr改行 セル範囲全体を 文字列値 として取得 sSrc = StrConv(sSrc, vbNarrow) ' 文字列値 半角を強制 Set oRE = CreateObject("VBScript.RegExp") ' RegExpをセット oRE.Global = True ' RegExpのお約束 oRE.Pattern = "(([^\r]*?)([GB]\d{4})([^\r]*?)|[^\r]*)" ' RegExpのマッチングパターン sSrc = oRE.Replace(sSrc, "$3") ' RegExpの置換(マッチする文字列以外を消す) Set oRE = Nothing ' ' ---------- 結果を複数列に出力する場合は以下10行イキ -------- 'Dim arrS() As String 'Dim i As Long ' arrS = Split(sSrc, vbCr) ' For i = 0 To UBound(arrS()) ' If Len(arrS(i)) > 5 Then ' arrS(i) = Format(arrS(i), "&&&&&" & Application.Rept(vbTab & "&&&&&", Len(arrS(i)) \ 5 - 1)) ' End If ' Next i ' sSrc = Join(arrS(), vbCr) ' Erase arrS() ' ' -------------------------------------------------------------- oData.Clear ' DataObjectの中身を一旦空に oData.SetText sSrc, 1 ' DataObjectに処理済の文字列をセット oData.PutInClipboard ' DataObjectの中身をクリップボードに送る Cells(1, "K").PasteSpecial ' クリップボードの中身をセル範囲に貼り付け Application.CutCopyMode = 0 ' コピーモードをキャンセル Set oData = Nothing Application.ScreenUpdating = True End Sub ' ' ================================================== 尚、何れのマクロも、出力先の先頭をK1に指定してあります。 "K" の行の記述を運用に合わせて指定し直してください。 何かあれば補足欄に書いてみてください。 それでは。。。
関連するQ&A
- 文字列操作(ExcelのVBAですが)
特定のExcelセル内にある文字列について、 例えば、 138.40.8.7 と入っている場合、 138.40.08.7 に、 35.9.1.6 と入っている場合、 35.09.01.6 にするVBA関数を作りたいのです。(セル関数でもかまわないですが) つまり、小数点で区切られている2番目と3番目の数字が 一桁の場合、頭に0を付けて、必ず二桁になるようにしたいわけです。 どなたか、ご教授くださいませ。 よろしくお願い致します。
- ベストアンサー
- Visual Basic
- (エクセル)表から1列の別表をつくりたい。
表に入力されたものを1列に並び替えをしたいのです。(エクセル関数) エクセルの表から、セルに入力された情報を抜き出し、並び替えたいのですが、行き詰ってしまい質問させていただきます。 (やりたいこと) 添付資料のように、事業所ごとに購入した物品が日付ごとに入力されていきます。この表を一列で並び替えることを したいのですが、現状の表の形で1列に抜き出すやり方が思い浮かびません。ひとつずつリンクを設定していけばいいですが、 それですと、空白のセルができてしまうこともあり、空白を消すためにフィルタをやらなくてはいけず、なんとか関数でどうにかできないと質問させていただきました(つまり空白のセルは飛ばし、隙間のない1列の表に変換したいです)。 (試したこと) (1)vlookup関数を使うために、日付の横に検索列を作ってもみましたが、同じ行に複数の抜き出すべくものがあると、 if関数のネストをいれるにも「if(c5="","",vlookup(v5,b5:r10,2,fasle)」みたくやってみましたが、c5までは取り出せても、 d5,e5,f5・・・と右にずらしていく関数式が思い当たりません。 (2)種類、数量データ入力されている全てのセルの横に(1.2.3.4.5.6.7.8.9.・・・)と数字をいれて検索列をつくり、vlookupとmatch関数の 組み合わせも試しましたが、vlookup関数の性質上、複数列に検索値(「vlookup(検索値,範囲,列番号,検索の型)」)が存在しているとこれも出来ず。 説明が不十分な点もあると思いますが、よろしくお願いいたします。もし、VBAでなければ難しいとのことでしたら、どのようなVBAを組めばいいかもお願いいたします。
- ベストアンサー
- オフィス系ソフト
- Excel 文字列にする関数
エクセルが苦手なので教えてください! 選別番号で6桁の数字を使っているんですが、頭の数字が0から 始まるものがあり、セルに入力するとその選別番号だけ5桁に なってしまいます。 文字列にすればいいということだけはわかっているのですが、 すでに数字が入っている列に対して、関数を使って以下のような ことはできますか? (1)選別番号が5桁だったら、頭に0をつけて6桁にする、 かつ文字列にする (2)選別番号がすでに6桁だったら、そのまま文字列に変換 宜しくお願いします!
- ベストアンサー
- オフィス系ソフト
- エクセル 関数 001 002と文字列で表示した
お世話になります。 XP/ エクセル2003 使用です。 エクセルの関数で、 数字を3桁で数字を表示したく 下記のように関数を組みました。 (セルの書式は、文字列にしています) 001の場合 =IF(A1="","","001") とし、001が問題なく返ってきます。 次のセルに、002と連番を振っていきたいので =IF(A2="","",B1+1) としましたが、 2 (002ではない) と返ってきます。(書式は文字列) 002、003、004・・・と続けていきたいのですが、 どのようにすればよろしいでしょうか? ご多用中恐れ入りますが、 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル2000 文字列の削除を関数でできますか?
1132 相川 1133 小笠原 A列のセルに上のよう入力されています。 「1132 相川」 でひとつのセルです。 関数を用いて「半角4桁の数字部分」と「半角の空白」を削除したいと思います。 1132 相川 1133 小笠原 ↓ 相川 小笠原 つまり上のようにしたいのです。 お時間の許す方にご回答をお願いしたいと思います。
- 締切済み
- オフィス系ソフト
- エクセルの質問です
A列は空白 B列に商品をあらわ5桁のコードが入っています。 C列にも同じく商品を表す5桁のコードが入っているのですが、 B列のセルと隣り合ったセルには同じ数字が入っていません。 D列には取引先の会社名が入っています。 B1に入ってる5桁の数字と同じ数字が入っている C列のセルを探し、 そのセルの隣のD列の会社名をA1のセルに表示させたいのですが、 どう関数を組んだらいいのでしょうか? A B C D 12345 12354 A社 12334 12345 B社 12443 12544 C社 上の表の場合B1とC2数字が同じなのでA1にB社と表示させたいのです。
- ベストアンサー
- オフィス系ソフト
- エクセル関数での文字列?の検索
ある行に0から5の一桁の数字が28列並んでいて、 その中で1の次にすぐ0が続いているかどうか (ふたつの隣接するセルが10かどうか)を調べる 関数はどうなりますか?
- ベストアンサー
- オフィス系ソフト
- 文字列の途中の空白を除く、また、A列の桁数に応じてB列に異なる値を記入する方法
初心者です。エクセル2000を使います。年は若くないです。 次のことで困っています。データが多いため、ひとつひとつを手で訂正できないのです。 1.セル内の文字列に含まれる空白(スペース)を除く方法 例えば、 東 いろは→→東いろは 空白を置換して除こうとしましたがうまくいきませんでした 2.A列の数字の桁数に応じて、B列に異なる数字を入れる方法。以前に教えていただいた方法+α が必要なのです。 A列の数字が 4桁 なら B列に 01を入力 A列の数字が 6桁 なら B列に 60を入力 A列の数字が 7桁 なら B列に 06を入力 A列の数字が 8桁 なら B列にA列の数字の上位2桁を入力 したいのです。前回次のような数式を教えてえていただきました。 =IF(LEN(A1)=4,"01",IF(LEN(A1)=6,"60",IF(LEN(A1)=7,"06",""))) 8桁のときの数式の組み立て(条件判断、表示の仕方)が分からず、困っています。教えて下さい。
- ベストアンサー
- オフィス系ソフト
- 特定の列の最初にあるデーター抽出
Sheet1のB5からB65までのセルにランダムで数字が入れてありますが必ずどのセルにも数字が入っているわけでは無く空白のセルも数字の入ったセルも混在しています。 この列の最後の数字をSheet2の任意のセルに抜き出すには、Lookup関数でできますが、列の最初の数字を抜き出すにはどのような関数で行えるのか教えてください。
- ベストアンサー
- その他MS Office製品
- エクセルで文字列の5桁目が9なら0に変更
エクセル初心者です。 インターネットで調べてみたのですが、同様の質問にヒットしませんでしたので、教えてください。 A列に8桁の会員番号が文字列で表示されています。 この8桁のうち右側の5桁を関数(RIGHT(a8,5))でB列に抽出しています。 抽出された5桁の数字ですが、最上位桁が9の場合、0に変えたいのです。 A B 01004567 04567 02015678 15678 03098765 98765 → 9を0に入替して「08765」と表示 このような対応が出来る関数を調べたのですが分かりませんでした。 ご教示のほど、よろしくお願いします。
- 締切済み
- オフィス系ソフト
お礼
何度もありがとうございます。 今回は、ご解説いただいたLike演算子を活用して Sub Sample2TEST() Dim i As Long, k As Long, str As String, time1 As Single time1 = Timer Application.ScreenUpdating = False For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row If InStr(StrConv(Cells(i, "B"), vbNarrow), "B") > 0 Or _ InStr(StrConv(Cells(i, "B"), vbNarrow), "G") > 0 Then For k = 1 To Len(Cells(i, "B")) str = Mid(StrConv(Cells(i, "B"), vbNarrow), k, 1) If str Like "[BG]" Then If Mid(Cells(i, "B"), k + 1, 4) Like "####" Then Cells(i, "D") = Mid(StrConv(Cells(i, "B"), vbNarrow), k, 5) Exit For End If End If Next k End If Next i Application.ScreenUpdating = True MsgBox Format(Timer - time1, "0.0000秒") End Sub とやってみることにしました。すみません。 また教えていた大方法は、じっくり勉強させていただいて身につけたいと思っております。 ありがとうございました。
補足
よく見直したら str = Mid(StrConv(Cells(i, "B"), vbNarrow), k, 1) If str Like "[BG]" Then If Mid(Cells(i, "B"), k + 1, 4) Like "####" Then は str = Mid(StrConv(Cells(i, "B"), vbNarrow), k, 5) If str Like "[BG]####" Then でいけますよね? 理解不足ですみません。