セル内に複数の単語。一定文字数を超えた単語を検出

質問

一つのセル内の複数の単語は、半角空白で区切られていて、数は1～4つでバラついています。

セルの中の単語で、一定文字数を超え単語を含むセルを検出し、セルに色を付けたいのです。

そのセルは、縦一列に並んでいるので、列内で検索、検出したいのです。

このようなマクロを教えて下さい。

セル内の単語が一つであれば、他にも方法はあるみたいですが、複数なので困っています。

お願い致します。

回答No.2

kagakusuki · ベストアンサー

>その列の行数は約4000ですが、数十ほど増減しますので、列の範囲を広めに設定しておけばよろしいですょうか？

　はい、列数ではなく、行数を広めに設定して下さい。

>範囲なしで、列まるごと指定できるならそうしたいと思います。

　一応、可能ではありますが、そうしてしまいますと、ファイルのデータ量が無駄に多くなりますので、出来れば手頃な行数にのみ、条件付き書式を設定される事を御勧め致します。

>バージョンは2003ですが、作成後に2007で開くこともあります。

>列はB列で、B2～B4000の範囲です。

>文字数は、半角アルファベットで14字以上です。

　回答No.1の方法を参考に、実際の状況に合わせて応用する方法を、質問者様は既に御解りになられている御様子ですが、念の為に、その方法に関して以下に記述致しておきます。
　尚、

>その列の行数は約4000ですが、数十ほど増減しますので、

との事ですので、下記の方法では条件付き書式を設定する範囲を、余裕を持たせてB2～B4200としております。

B2セルを選択
　　↓
メニューの[書式]をクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に次の数式を入力

=SUMPRODUCT((FIND(CHAR(1),SUBSTITUTE(B2&" "," ",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(B2)-LEN(SUBSTITUTE(B2," ",))+1))))-FIND(CHAR(1),SUBSTITUTE(" "&B2," ",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(B2)-LEN(SUBSTITUTE(B2," ",))+1))))>13)*1)

　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中から、セルを塗りつぶしたい色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
B2セルを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
B3～B4200のセル範囲をまとめて範囲選択
（この様に、選択範囲が広い場合には、名前ボックスに

b3:b4200

等と入力してから[Enter]キーを押すと、手早く選択する事が出来ます）
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ダイアログボックスの中にある「書式」と記されている箇所をクリックして、チェックを入れる
　　↓
「形式を選択して貼り付け」ダイアログボックスの[OK]ボタンをクリック

　尚、データが入力されている行数が増えて、4200行でも行数が心もとなくなって来た場合には、B2～B4200のセル範囲内の適当なセル1つをコピーしてから、新たに条件付き書式を設定したいセル範囲に、[形式を選択して貼り付け]機能を使用して書式のみを貼り付けて下さい。

回答No.1

kagakusuki

マクロなど使わずとも、条件付き書式だけでも可能です。
　質問者様が御使いになられているExcelのバージョンが不明ですので、取り敢えずはExcel2007よりも前のバージョンと、Excel2007以降のバージョンの両方の場合における設定方法を記載させて頂きます。
　又、質問者様の仰る「そのセル」とは、何行目から何行目の範囲のセルであるのかも不明ですし、質問者様の仰る「列内」も何列の事なのかが不明ですので、ここでは仮に、A1～A99のセル範囲内のセルの中で、6文字以上の単語を含むセルのみに色を付けるものとします。

【Excel2007以降のバージョンの場合】

Excelウインドウの[ホーム]タブをクリック
　　↓
A1セルを選択
　　↓
「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=SUMPRODUCT((FIND(CHAR(1),SUBSTITUTE(A1&" "," ",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(A1)-LEN(SUBSTITUTE(A1," ",))+1))))-FIND(CHAR(1),SUBSTITUTE(" "&A1," ",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(A1)-LEN(SUBSTITUTE(A1," ",))+1))))>5)*1)

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中から、セルを塗りつぶしたい色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「ルール(表示順で適用)」欄が「数式:=SUMPRO..」となっている行の「適用先」欄をクリック
　　↓
A1～A99のセル範囲をまとめて範囲選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

【Excel2007よりも前のバージョンの場合】
A1セルを選択
　　↓
メニューの[書式]をクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に次の数式を入力

=SUMPRODUCT((FIND(CHAR(1),SUBSTITUTE(A1&" "," ",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(A1)-LEN(SUBSTITUTE(A1," ",))+1))))-FIND(CHAR(1),SUBSTITUTE(" "&A1," ",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(A1)-LEN(SUBSTITUTE(A1," ",))+1))))>5)*1)

　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中から、セルを塗りつぶしたい色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
A1セルを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
A1～A99のセル範囲をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ダイアログボックスの中にある「書式」と記されている箇所をクリックして、チェックを入れる
　　↓
「形式を選択して貼り付け」ダイアログボックスの[OK]ボタンをクリック

補足

早速ありがとうございます。

バージョンは2003ですが、作成後に2007で開くこともあります。

列はB列で、B2～B4000の範囲です。

文字数は、半角アルファベットで14字以上です。

その列の行数は約4000ですが、数十ほど増減しますので、列の範囲を広めに設定しておけばよろしいですょうか？

範囲なしで、列まるごと指定できるならそうしたいと思います。

出先からですので、取り急ぎ補足説明とお礼まで。

実際に動作確認できましたら、改めて報告させて頂きます。

ありがとうございました。