Excelでアスタリスクを置換するには？記号・範囲とセル全体の一致を確認

※本記事にはPRを含みます。

こんにちは。「脱会社員」脱サラを応援し続けるブログ、運営者の「なかびー」です。

Excelで商品名や管理番号の「*」だけを消そうとして、検索する文字列にそのままアスタリスクを入れると、思っていたより多くの文字が置き換わることがあります。検索置換で使う「*」には、記号としての見た目だけでなく、任意の文字列を表す機能があるからです。記号そのものを探すときは「~*」、疑問符なら「~?」、チルダなら「~~」と、先頭へチルダを付けます。

ただし、その入力だけで目的に合うとは限りません。「A*01」の中の星だけを替えるのか、そのコード全体を別のコードへ替えるのかでも設定が違います。この記事では、Windows版Excelで架空の9行を使い、原本1冊と6つの置換条件、計7ブックを比較します。対象の列、セル全体との一致、数式を残す範囲を分け、直したい文字以外を変更しない確認手順を紹介します。

アスタリスクそのものと、文字列を探すパターンを分ける

表の中で見えるアスタリスクは、一つの文字です。例えば「A*01」という管理用のコードでは、星も含めて一つの値になっています。一方、Excelの検索に使う「*」は、任意の文字列を表すワイルドカードです。「A」で始まる何かを探したい場面と、コード内の星を探したい場面を同じ入力で済ませようとすると、対象が変わってしまいます。

Microsoftの検索で使うワイルドカードの説明には、アスタリスク、疑問符、チルダの役割が示されています。「?」は任意の1文字、「*」は任意の文字列を表します。「~」は、これらの記号を文字として探したいときに直前へ付けるために使います。最初に、画面にある形を探すのか、条件に合う値を探すのかを決めましょう。

「?」を入力したのに疑問符以外が選ばれる理由

今回の例には「A*01」「A?01」「A~01」「AB01」があります。セル全体が「A?01」というパターンと一致する条件で置換すると、4つとも対象になりました。Aと01の間には、それぞれ星、疑問符、チルダ、Bという1文字があります。検索欄の「?」は、その1文字が何であるかを指定していないのです。

一方で「A**01」は間に2文字あり、「A*01-old」はセルの末尾に別の文字列が続いています。今回のセル全体一致の試験では、どちらもそのパターンの対象にはなりませんでした。「疑問符で探したら星のある行まで変わった」ときは、検索機能の異常と考える前に、疑問符を文字として探す指定にしたかを確認します。

「~」に見える文字を使うときも、見た目だけで判断しません。全角の波形や、長音の記号を代わりに入れていないかを確認します。ここで示すのは半角のチルダです。検索欄へ入れる文字が分からなくなったら、仕事のデータをそのまま試し続けず、短い架空のコードを別のブックに置き、どの行が一致するかを先に見てください。

なお、ファイル名の検索、Excelの検索置換、関数に渡す文字列では、似た記号でも解釈が同じとは限りません。検索欄で使ったチルダ付きの入力を、そのまま別の関数へ移す手順にはしません。今使っている機能で何を意味するのかを確認し、実際の一つの結果と照合することが大切です。

置換する列を選び、条件と元の値を確認する

一括で書き換える前に、元のブックを残した作業用コピーを用意します。今回の架空表では、A列が確認用ID、B列がコード、C列がコードから星を除いた結果を表示する数式です。置換の対象は複数セルを含むB2:B10に限定しました。IDや計算式の列まで選択する必要はありません。

実際の操作では、対象となる連続範囲を選んでから［ホーム］の検索機能、または Ctrl＋H などで［検索と置換］を開きます。アプリの版によって表示が異なる場合は、Microsoftの検索と置換の案内で操作を確認できます。今回直したい列のどの行からどの行までかを、先にメモにしておくと範囲を確かめやすくなります。

一つのセルを選んだ状態と、複数セルの範囲を区別する

セルを一つクリックしただけで、表のその列全体を選んだことにはなりません。今回のように対象が9行なら、B2からB10までの複数セルを範囲として選びます。見出し、別表、下の注意書きまで含めたいのかを確認してください。選んだ範囲の中に、意図しない数式や別の一覧が混ざっていれば、検索文字列が正しくても書き換える場所は合いません。

範囲を絞る理由は、処理を速くするためだけではありません。同じ「*」が商品名の飾り、別表の注意書き、数式内の文字列にある場合、同じ意味とは限りません。コードの星だけを直す作業で、ブック全体を対象にする必要があるのかを考えます。特定のシートだけを使うと決めたら、そのシート名も確認します。

［オプション］を開ける場合は、検索する範囲や一致条件、書式条件が残っていないかを読みます。前の作業で大文字と小文字を区別した、セル全体と一致させた、特定の書式だけを探したなどの条件が、そのまま次の作業に合うとは限りません。検索対象やオプションの選択肢はタブや版によって違うので、見えない選択肢があると決めつけず、今の置換画面で確認できる条件を控えます。

置換する文字列を空にするときは、置換後の欄に以前の入力や半角スペースが残っていないかを見ます。「消すつもりだったのに空白が入った」という結果と、「検索に一致しなかった」という結果は別です。まず一つのセルで置換し、数式バーにある実際の値を読みます。見た目の隙間だけでは、空白文字が追加されたかどうか分からない場合があります。

変更前の値と期待する値は、少なくとも対象になる例と対象にならない例を一つずつ用意します。例えば「A*01」は「A-01」へ変えるが、「AB01」は残すという二行です。全部が置換対象の表だけで試すと、対象外の行まで変わる設定を見逃しやすくなります。今回の表には空欄と、星だけが入るセルも置いて、通常のコード以外の行も確認しました。

確認依頼で渡すのは、検索欄へ入れた文字と元の値

画面だけを共有する場合、セルの「A*01」と検索欄の「A~*01」を同じ文字列だと思われることがあります。前者は元データで、後者は星を文字として扱うための検索指定です。受け取り側に「元コードへチルダを追加してください」と依頼したつもりはないため、二つの欄を分けて見せます。今回なら、元データはA*01、検索指定はA~*01、一致条件はセル全体、置換後はAPPROVEDという四項目にできます。

記号の置換を頼まれた人が複数いる場合も、共有するのは作業用コピーとこの四項目です。検索欄の例だけを送って「これで全部直して」と伝えると、範囲や一致条件が別の状態で実行されるかもしれません。同じ設定で処理したかを確認したいときは、元の特徴が分かる一行と、対象外として残るべき一行の変更前後も一緒に残します。

今回の元データでは、星を替える条件を使った後もA?01とA~01は残ります。処理のあと、その二行がハイフンへ変わっていたら、星だけを扱った結果とは一致しません。見た目をきれいにするという大まかな依頼であっても、どの文字を替えるかが決まっていなければ、段階を分けます。星を確認してから疑問符を扱うというように、判断と結果を一組ずつ残すと、問題のある処理だけを見直せます。

9行の実例で、星・疑問符・チルダの変化を比べる

Windows版Excelで、同じ元データを入れた架空ブックを条件ごとに作成し、検索置換の結果を保存しました。置換を試す6ブックは、B列の対象範囲だけを書き換えています。検索する文字列を「~*」、置換する文字列を半角ハイフンにし、セル内の一部も対象にした場合の変化は次のとおりです。

星が二つある「A**01」は、二つともハイフンになっています。「一つのセルで一回だけ変わる」という結果ではありません。また、末尾に「-old」がある行でも、セル内の星は対象です。星だけを消したい場合は、同じ検索条件で置換後を空欄にする考え方ですが、その結果が別の既存コードと重ならないかも見ます。見出しや商品名の飾りを外す作業と、管理番号そのものを変える作業では確認の意味が違います。

検索欄に「*」だけを入れた対照例

検索する文字列を裸の「*」、置換後を「FIX」にした対照ブックでは、空欄を除く8セルがすべて「FIX」になりました。「AB01」のように星の記号がないコードも対象でした。任意の文字列という意味で一致したためです。これは架空ブックで意図的に違いを確認した例で、実際の管理表でそのまま試す操作ではありません。

疑問符の記号だけをハイフンにしたい場合は「~?」で探します。今回の元データでは「A?01」だけが「A-01」になりました。チルダの記号だけなら「~~」で探し、「A~01」だけが変化しました。結果が似た見た目になっても、変更される行は違います。変えた後の一行だけでなく、元の値と対象外の行を並べて見てください。

「A*01」「A?01」「A~01」をそれぞれハイフンに変えれば、どれも「A-01」になり得ます。別々の元コードが同じコードに揃うことが、今回の目的に合うかが大切です。コードの表記を整えるだけなら問題ない場合もありますが、別の商品を識別する番号なら見た目の統一で区別を失うことがあります。置換操作が成功したことと、表全体の識別が保たれたことを別に判定します。

今回の比較は、明示した範囲と条件で検索置換した結果です。検索ダイアログのすべての選択肢、フィルター中の表、非表示行、保護されたシートを試したものではありません。自分のブックにその条件があるときは、今回の9行と同じ結果がそのまま全体へ当てはまると考えず、同じ構造の作業用コピーで確認します。

「何行変わるはずか」を元データから数える

今回の9行で星があるのは、W01、W05、W06、W07、W09の5セルです。ただしW05には星が二つあるため、星の文字は全部で6個あります。置換したセル数と、替わった記号の個数は同じではありません。「5セルを変更」と「6個の星を変更」はどちらもこの元データに対応します。担当者と件数を比べるときは、セルを数えたのか、文字を数えたのかを揃えます。

裸の星を使った対照例では、記号がないW04まで含む8セルが変更されました。空欄のW08は空欄のままでした。この差から、5セルだけが変わるはずの操作に対して8セルが変われば、対象の指定を見直す必要があると分かります。Excelのメッセージに出る数の解釈を決めつけず、元のIDに沿って、どのセルの値が違うかを照合します。

コード全体を替えるときは、セル全体の一致も確かめる

星を一文字だけ替える作業と、「A*01」という値を丸ごと別の値に替える作業は違います。今回、検索する文字列を「A~*01」、置換後を「APPROVED」とし、セル全体と一致する条件で試すと、「A*01」の一行だけが変わりました。「A**01」や「A*01-old」はそのまま残りました。星を文字として扱う指定に加えて、セルの中に一致する部分があればよいのか、セル全体が一致しなければならないのかを決めています。

「一部を含む」と「その値である」を言葉にする

依頼が「A*01を新コードにしてください」だけなら、A*01-oldという旧版の行も含めるかを先に確かめます。文字列の一部が同じという理由で古い版まで更新すると、旧版を区別するメモの意味が変わるかもしれません。反対に「星という飾りをすべて除く」が目的なら、コード全体の一致だけにすると、別のコードにある星を取り残します。実行前に対象を言葉で書くと、必要な設定も決めやすくなります。

確認メモなら「B2:B10のうち、値がA*01のセルをAPPROVEDに変更。A*01-oldは保持」と書けます。星だけを消すなら「B2:B10の全セル内にある星だけを削除。他の文字と空欄は保持」です。二つは似た依頼に見えても、検索欄だけではなく、一致条件と期待する結果が違います。担当者に確認するときも、設定名だけを聞くより、この前後の例を見せる方が意図を合わせやすくなります。

入力前後の余分な空白、全角と半角、大文字と小文字をどこまで同じ扱いにするかも、別の条件です。例えば「A*01」の後ろに空白があるセルを完全一致の対象に含めるかは、今回の空白なしの表だけでは分かりません。見えている値が同じように見えても、数式バーや必要な確認式で元の内容を読んでから、その処理を追加します。星の置換と同時に、空白や大文字まで一度に整えようとすると、どの操作で値が変わったかを見失いやすくなります。

すべて置換の前に、対象外の行を一つ見る

まず目当てのセルを検索し、次に似ているが残すべき行が該当しないかを確認します。今回なら、A*01をコード全体で替える条件に対して、A*01-oldを残すべき対照にできます。A**01は星の数が違い、A?01は記号の種類が違うため、それぞれ別の確認にも使えます。対象になるセルだけが並ぶ表では、その境界を確かめることができません。

対象が複数シートにある場合も、最初に一つの範囲で意図と合うことを確認します。シートごとに列の役割が違えば、同じB列でも別のデータかもしれません。確認できた設定をメモしてから、次のシートで同じ範囲と内容が成り立つかを見ます。最初の表で合格したという理由で、別のブックを全選択して一括処理する必要はありません。

一致条件を変えて試し直すときは、変更済みの表をそのまま次の試験の元にしないことも大切です。今回の7ブックは、条件ごとに同じ元データから作っています。裸の星で全体がFIXになった表を、次に「~*」で直そうとしても、元の星やコードは残っていません。比較するときは、必ず元のコピーから条件を一つ変えて、変更前と後を並べます。

条件ごとの変更セル数と、残ったコードを照合する

この表は、保存した各ブックのB列を元の9行と比較したセル数です。検索画面のメッセージを記録した数字ではありません。一つのセルで同じ記号を複数個替えた場合も、そのセルは一つとして数えています。確認用の表で差が出た理由を理解してから、実際の仕事のデータへ進みます。

例えば全体一致のA?01では4セルに同じMATCHが入ります。いくつかの別コードを共通の仮表示にする実験なので、正式な商品表にそのまま採用するための結果ではありません。「同じパターンへ当てはまる値を洗い出したい」だけなら、まず検索結果を読む作業で足りる場合があります。調べる段階から置換を実行する必要があるかも、目的に合わせて決めます。

元の列の書換えと、数式で表示する結果を区別する

表の用途によっては、元の値を残し、星を取り除いた見せ方だけを別の列に作りたい場合があります。今回の架空表では、C2に =IF(B2="","",SUBSTITUTE(B2,"*","")) という式を置き、下の行も同じ関係にしています。SUBSTITUTEのこの使い方では、指定した文字列の星を空文字へ替えた結果が返ります。検索ダイアログで使う「~*」を、そのままこの式へ入れたわけではありません。

元のB2がA*01なら、C2に出る結果はA01です。B列にある元の星を含む値は残ります。元がA**01でも、この式の結果はA01になりました。C列の見た目が揃ったことは、二つの元コードが同じものを指すという意味ではありません。作業の目的が表記を比較するための補助なら、その列を補助列として使い、照合に使う正式なIDと分けて扱います。

結果が空に見える二つの行を同一視しない

今回、元のB列が空欄のW08と、星だけがあるW09では、C列はどちらも空に見える結果になりました。しかし、元のデータは違います。未入力と、何かが入っていて加工の結果が空になる状態を同じものとして数えると、確認待ちの行を見落とすことがあります。原本でどちらだったかを知りたい場合は、C列だけで判断せずB列とIDも残します。

原本と、検索置換をB列だけで実行した6ブックでは、C列の数式とA列の確認用IDが変わっていないことを、保存したブックを別に開いて確認しました。C列の計算結果は、B列を書き換えれば変わります。例えば星をハイフンへ替えたB列がA-01なら、C列もA-01です。数式自体を保持したことと、式の結果が元と同じであることは別です。

数式の中に星の文字が入っていることも、対象範囲を分ける理由になります。今回のC列の式には、星を文字列として指定する部分があります。コードだけを変更するなら、計算式がある列へまで検索置換を広げる必要はありません。「表に星が見えるから」という理由で全部を選ぶ前に、数式バーにある式と、表示されている結果のどちらを見ているかを確かめます。

別の表へ渡すときに、どの列を正式な値にするか

加工結果を別システムへ入力するために使う場合、元のコードと加工したコードのどちらが受け取り先の仕様かを確認します。元のデータを一律に上書きする必要がなければ、補助列を使ったまま検査し、引き渡す列を選べます。二つの列に同じ見出しを付けると混乱しやすいため、「受領コード」「比較用コード」のように用途を分けます。

受け取り先が星を許可しない場合でも、勝手に消すとコードが別の商品と重なることがあります。A*01とA**01が同じA01になる例のように、元へ戻せない変換です。変換後に重複する結果がないか、元の識別と対応が取れるかを見ます。自分だけで決められないルールなら、必要な二行を示して、受け取り先と正式な変換方法を合わせます。

比較用に作った式を値として貼り付けると、元のB列を参照する関係はなくなります。確定するために値へ替える作業が必要かは、更新の運用によります。今回の検証では、そのような値貼り付けによる確定操作は試していません。数式で出した結果なのか、確定した文字列なのかを分けて引き継ぎ、必要な変更だけを行います。

保存前後をIDで比べ、元の表と対応を残す

置換が終わったら、作業した範囲と元の値をIDで照合します。「何件置換しました」という表示だけでは、残すべき行が保たれたかまでは分かりません。今回の例では、対象になったコードだけでなく、W02の疑問符、W03のチルダ、W04の星がないコード、W08の空欄も確認します。検索条件に合うものを替えたことと、対象外を保ったことを別の欄にします。

1. 元ブックと作業用コピーの名前・保存場所を区別する。
2. 対象のシートと複数セル範囲を確認し、対象外の列を選ばない。
3. 記号の指定とセル全体の一致条件を、変更前後の例に合わせる。
4. 対象になる行と、似ているが残す行を一つずつ確認する。
5. ID、元のコード、変更後のコード、関連数式を保存前後で比べる。
6. 同じコードにまとまった行や空に見える結果を確かめてから共有する。

一括処理の前に使う、確認用の短い表

確認用には、実際に出てくる特徴を少しずつ入れた表を作ります。星が一つのコード、二つのコード、別記号のコード、末尾に版名があるコード、空欄を入れると、どこまでを同じ扱いにしたかを説明できます。実データを使えない場所では、今回のような架空の値へ置き換えて試せます。ただし、架空の表に入れていない条件まで検証できたとは扱いません。

例えば、元ブックに全角の記号や数式で返る文字列が混ざる場合は、その特徴を一行だけ追加して確かめます。フィルターの有無や非表示行の扱いなども、必要なら別に試験します。こうした条件を確認していないまま、「星はこれで必ず全部消える」と判断するのではなく、今回作業する範囲で一致するかを見ます。

データを保存したら、共有する方のファイルを開き直し、元のブックを見ていないかを名前と保存先で確認します。IDの順番や行数が変わっていないか、別の表を編集していないかも見ます。今回の7つのブックではIDを保持して比較できたため、どの条件でどの値が変わったかを追えました。自分の作業でも、変更の前後を結び付けるIDを残すと、後から指摘が来たときに探し直しやすくなります。

取り込み段階の問題と、文字の置換を分ける

CSVなどから取り込んだ元データが文字化けしていたり、番号の先頭のゼロを失っていたりする場合、見えている星を消すだけでは元の内容を復元できません。取り込み時点で日本語や番号を確認する方法は、ExcelでCSVが文字化けしたときの原本との照合手順で紹介しています。まず元のデータを正しく読めているか、その後に今回の表記調整が必要かを判断します。

置換後のコードを別の表へ照合する場合も、加工前のデータと元の受領ファイルを残します。例えば取引先から受け取った「A*01」を、自社の表示で「A01」と見せているなら、その対応を確認できるようにしておきます。元の記号に意味があるか分からない場合は、見た目を整える目的だけで正式なコードを上書きしません。

やり直す際に残す、原本と作業記録

置換した直後に想定外の値へ変わったと気づいた場合は、別の文字を上から入れて推測で直す前に、元に戻す操作を使えるか、原本のコピーを開き直せるかを確認します。元のコードを失った状態で、記憶から星や末尾の版名を足すと、違う行を作るおそれがあります。保存や終了を挟んだ後にも同じように元へ戻せると考えず、変更前のファイルを保持しておきます。

受け取ったデータが後から増える場合は、今回置換した行の最終IDをメモにします。例えばW09までを確認済みとし、次回追加された行は同じ処理をしてよいかを確かめます。途中へ新しい行が入る運用なら、行番号だけで追うのではなく、コードと固有IDを組にして変更範囲を残します。作業記録を残す目的は、処理した数を増やすことではなく、確認できた範囲を次の人が再現できるようにすることです。

今回の結果は、検証用プログラムからWindows版ExcelのRange.Replaceを実行し、元データ9行へ範囲と検索条件を明示して得たものです。検索画面のクリック操作そのものは未検証で、画面の手順はMicrosoftの案内と区別しています。保存後には、コードの値だけでなくA列のIDとC列の数式も照合しました。共有前の点検では、自分の表で残したい関係も同じように確認項目へ入れます。

星を消したいときは、検索欄の「~*」だけを覚えるのではなく、対象の範囲と一致条件を組にします。コード全体を替えるのか、一部の記号だけを替えるのかを決め、対象外の行も一つ確認しましょう。元の値と加工した結果を分けて残せば、一括処理のあとも、何を変えたかを説明できます。