エクセルの文章からリストの番号だけ取り出したい

質問

こんにちわ、、
このサイトはいつも利用させて頂いていつも大助かりです。友達に質問されて本を読んだりしたのですが持っている本じゃ役に立たないので、もしわかる方がいらっしゃいましたら参考になるURL、解決策とう教えてください。
宜しくお願い致します。

うまくいえないのですが。
↓のようなりストを元に（別シートで）
(1)黄色
(2)赤
(3)紫

一つのセルの文章の中に・・・

＜赤＞い夕日の光と＜黄色＞い花が咲いているそばに＜紫＞がかった夜の地平線がバックにとてもきれいだった。（例文）※＜＞は元から無い、わかりやすくつけました。

とあったら

隣のセル（列）に
(1)(2)(3)　　のみ吐き出す関数ってありますでしょうか？

もちろん、
例文２：　＜赤＞いサンダルに＜赤＞いワンピースはかわいかった。＜黄色＞い帽子はイマイチだった。
↓
(1)(2)(2)　　
という感じに同じ言葉があった場合、数字も二つはきだしたいです。

回答No.3

ja7awu · ベストアンサー

数を算出するのではなく、指定した文字列が、検索されたら、「その記号を
列記する」　ということですよね。

そうだとすると、一般の組込み関数を使用する方法では、ワーク用の列を
一杯使うことになり計算式もかなり複雑になり大変です。

特に例文2の(1)(2)(2)のように同じ文字列を複数検索する場合は、かなり
複雑になり、困難と言える程と思います。

これを簡単に処理するためには、ユーザー定義関数を使用することです。
それで良ければ、詳しい操作手順を書きますのでテストしてみてください。

【 操作手順 】

・Sheet2にリストの表を作ります。
・１行目は、見出しで、A2に　(1) <--- 先頭に文字列の ' を入れて入力します。
・必要分下にフィルハンドルコピーします。
・B2 に"黄色"を入れ、下方向に必要なデータを入れます。(数量は任意。下記4項参照)

【 ユーザー定義関数 StrExtract の設定方法 】

１．Alt + F11 で VBE(Visual Basic Editor)を開きます。

２．VBE のメニューから[挿入] -->[標準モジュール] を指定します。

３．モジュールウィンドウに下記コード(16行)をコピーして貼り付けます。
　　（Generalの下、Option Explicitがあったら、その下に）

４．6行目で、参照する表のシート名と検索する「文字列」の種類を指定している範囲を
　　必要により変更します。現在は"Sheet2"のB2:B10になっています。

５．Alt + Q （または、右上隅の×）でウィンドウを閉じ、シートに戻ります。

　以上でユーザー定義関数の設定は、完了です。

【 使い方の例 】

・ A2に例1の文章があるとして　B2に　=StrExtract(A2)　を入力すると、
　(1)(2)(3) が表示されます。

・ この式を下にコピーすると
　A3に例2の文章があるとして B3には、(1)(2)(2) が表示されます。

このように、一般の関数と同じように使います。

これで如何でしょうか。

Function StrExtract(StrWrit As String) As String
Dim Rng As Range
Dim R As Range
Dim StrOut As String
Application.Volatile
For Each Rng In Worksheets("Sheet2").Range("B2:B10")
　　Do
　　　　If IsEmpty(Rng.Value) Then Exit Do
　　　　If InStr(StrWrit, Rng.Value) > 0 Then
　　　　　　StrOut = StrOut & Rng.Offset(, -1).Value
　　　　　　StrWrit = Replace(StrWrit, Rng.Value, "", 1, 1, vbTextCompare)
　　　　End If
　　Loop Until InStr(StrWrit, Rng.Value) = 0
Next Rng
StrExtract = StrOut
End Function

お礼

こんばんわ！
できましたよ！！！本当に助かりました！＾－＾
エクセルは２０００で
ＯＳも２０００です。

回答No.5

imogasi

＃３の回答のようにＶＢＡで無いと無理でしょう。
ちょっと問題を変えて、その文字があればその番号をだし
無ければ「なし」をだすなら、
Ｓｈｅｅｔ１のＡ１に
＜赤＞い夕日の光と＜黄色＞い花が咲いているそばに＜紫＞がかった夜の地平線がバックにとてもきれい赤だった。があって
Ｓｈｅｅｔ２のＡ１：Ａ７に
赤
黄色
紫
大阪
夜
地平
夜行
とある時
Ｓｈｅｅｔ１のＤ１に
=IF(ISERROR(FIND(Sheet2!A1,$A$1)),"なし",ROW())
といれ、下に複写します。
Ｄ１：Ｄ７に
1
2
3
なし
5
6
なし
となります。赤、赤と２度３度出てきたときに１，１と出すのが難しいです。
セル内の文字の検索はＦＩＮＤ、ＳＥＲＣＨしかありませんから、工夫のしようがない。１つ目の「赤」を消さないと、２つ目の赤の存在に到達できない。

お礼

回答ありがとうございます。
会社内でいろいろ注文があったりと、教えていただいた方法を試してみたりとたくさんデータがあるエクセルを操作することは大変な事でした＞＜

回答No.4

ja7awu

No.3ですが・・・
あれッ　Excelのバージョンを確認せずに書き込みましたが、
Excel2000か、それ以降で動作します。

97の場合は、コードの変更が必要です。

回答No.2

nihonjinn

色のリストはA1に「(1)」、A2に「黄色」と入れて、行方向にリストを作っておきます（シートの名前を「リスト」としておきます）

文章が別のシートのA1からA列に並んでいるなら、<黄色>の数を出す式は
=(LEN($A1)-LEN(SUBSTITUTE($A1,リスト!A$2,"")))/LEN(リスト!A$2)
となります。この式を文章が入っているシートのC1に入れておき、D1,E1にコピーすると続けて赤、紫の数も出すことができます。
文章があるシートのB1に
=REPT(リスト!$A$1,C1)&REPT(リスト!$B$1,D1)&REPT(リスト!$C$1,E1)
と入れると(1)(2)(3)と出すことができます。後はこれらのセルを列方向に向かってコピーするだけです。
リストの数が多いと大変ですががんばってください。C列、D列、E列は表示を隠しておけばいいでしょう。

補足

実は、、色　というのは例であって　項目は１００個くらいあって行方向に１００は見づらいのです＞＜

列A1に名称　列A2　に　１～１００の数字をのせて試してみたいと思うのですが・・・重ね重ねわがままばかりすいません・・・

よって、
シート１（文章列がある）のA列に文章の列
B列に数字をはきだす列←に式を入れればいいんですよね？

シート２（リスト）のA列に「色」B列に「１～１００」

という感じでいけばよいでしょうか？？・・

回答No.1

matrix4

vlookup関数で一から表を作ってもってくるか。
または、
A1 赤
A2 黄
A3 紫
　だとして、B2に赤なら=A1,黄なら=A2,紫なら=A3と打てば出るのでは・・。

補足

A1赤
A2黄
A3紫

B2に赤ならA1
　　　　　A2
　　　　　・
　　　　　・　
という式は、IF関数をリストの数だけつなげて作り一つのセルに設定するという意味でしょうか？