Heading

Just another blog

How To Remove Text Before Or After a Specific Character In Excel

Excelでテキストデータを扱っていると、特定の文字や文字列の前後のテキストを削除する必要がある場合があります。

コンマの後のテキストが削除されたデータセット

単純な数式や簡単な検索と置換でできる場合もあれば、より複雑な数式や回避策が必要な場合もあります。

このチュートリアルでは、Excel で特定の文字の前後にあるテキストを削除する方法を (さまざまな例を使って) ご紹介します。

それでは、いくつかの簡単な例から始めましょう。

This Tutorial Covers:

Remove Text After a Character Using Find and Replace

特定の文字列の後（または文字列の前）にあるすべてのテキストをすばやく削除したい場合は、「検索と置換」とワイルドカード文字を使って行うことができます。

例えば、以下のようなデータセットがあり、コンマ文字の後の指定を削除し、コンマの前のテキストを残したいとします。

特定の文字の後のテキストを削除する必要のあるデータセット

以下にその手順を示します。

  1. A列からB列にデータをコピー＆ペーストする（これは元のデータも残すためです）A列からB列にデータをコピー＆ペーストする
  2. B列のセルを選択した状態で、「ホーム」タブをクリックします。 ホーム」タブをクリック「ホーム」タブをクリック
  3. 「編集」グループで、「検索」&「選択」オプションをクリック
  4. ドロップダウンで表示されるオプションで、「置換」オプションをクリックします。 検索と置換」ダイアログボックスが表示されますClick on the Replace option 「置換」オプションをクリックします
  5. 「何を検索するか」フィールドに、「*」（例, 「検索対象」フィールドにカンマとアスタリスクを入力します
  6. 「置換後の文字列」フィールドを空欄にします「置換後の文字列」を空欄にします
  7. 「置換後の文字列」フィールドを空欄にします。
  8. 「Replace All」ボタンをクリック「Replace All」をクリック

上記の手順では、データセット内のカンマを見つけ、カンマの後のすべてのテキスト（カンマを含む）を削除します。

カンマ以降のテキストが削除されたデータセット

これは選択されたセルのテキストを置き換えるものなので、テキストを別の列にコピーしてからこの検索と置換の操作を行うか、元のデータがそのまま残るようにデータのバックアップ コピーを作成することをお勧めします

これはどのように動作するのでしょうか。

* (アスタリスク記号) は、任意の数の文字を表すことができるワイルドカード文字です。

これを (「何を検索するか」フィールドの) カンマの後に使用してから [すべてを置換] ボタンをクリックすると、セル内の最初のカンマが検出され、それが一致したとみなされます。

これは、アスタリスク (*) 記号が、カンマに続くテキスト文字列全体にマッチすると考えられるからです。

そのため、「すべてを置換」ボタンをクリックすると、カンマとそれに続くすべてのテキストが置換されます。 複数のコンマがある場合、この方法は常に最初のコンマを見つけ、それ以降をすべて削除します。 そのため、2つ目のコンマの後のテキストをすべて置き換え、1つ目のコンマはそのままにしておきたい場合は、このメソッドを使用できません。

コンマの前のすべての文字を削除したい場合は、「検索対象」フィールドの入力を変更し、コンマの前にアスタリスク記号を付けます (「*」の代わりに「*」)

数式を使用したテキストの削除

特定の文字の前後にあるテキストを検索して置換する方法をより細かく制御する必要がある場合は、Excel に内蔵されているテキスト数式を使用するのがよいでしょう。

たとえば、以下のようなデータセットがあるとします。 コンマの後のテキストをすべて削除したいとします。

特定の文字の後のテキストを削除する必要のあるデータセット

以下はこれを行うための数式です。

=LEFT(A2,FIND(",",A2)-1)

カンマの後を削除する数式

上記の数式では、FIND関数を使用してセル内のカンマの位置を求めます。

この位置の番号は、LEFT関数でカンマの前のすべての文字を抽出するために使用されます。

これは簡単なシナリオでしたが、

少し複雑なシナリオを見てみましょう。

次のデータ セットがあり、2 番目のコンマの後のテキストをすべて削除したいとします。

2つのコンマがあるデータセット

これを実行するための数式は次のとおりです。

=LEFT(A2,FIND("!",SUBSTITUTE(A2,",","!",2))-1)

Formula to remove text after the second comma

このデータ セットには複数のコンマがあるため、FIND 関数を使用して最初のコンマの位置を取得し、その左側のすべてを抽出することはできません。

何とかして 2 番目のコンマの位置を見つけ出し、2 番目のコンマの左側にあるものをすべて抽出する必要があります。

そのために、SUBSTITUTE 関数を使用して 2 番目のコンマを感嘆符に変更しました。 これで、セル内にユニークな文字ができました。

このエクスクラメーション マークの位置は、LEFT 関数で 2 番目のコンマの前のすべてを抽出するために使用されます。

ここまではいいですね！

しかし、データ セット内のコンマの数が一貫していない場合はどうでしょう。

例えば、以下のデータセットでは、コンマが2つのセルと3つのセルがあり、最後のコンマの前にあるすべてのテキストを抽出する必要があります。

そのための数式を以下に示します

=LEFT(A2,FIND("!",SUBSTITUTE(A2,",","!",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)

コンマの最後に出現した後のテキストを削除する数式

上記の数式では、LEN関数を使用して、セル内のテキストの全体的な長さと、コンマなしのテキストの長さを求めています。

この 2 つの値を引くと、セル内のカンマの合計数が得られます。

つまり、セル A2 には 3、セル A4 には 2 が得られます。

この値を SUBSTITUTE 式内で使用して、最後のカンマをエクスクラメーション マークに置き換えます。

この値を SUBSTITUTE 式の中で使用し、最後のカンマを感嘆符に置き換えます。そして、左の関数を使用して、感嘆符の左側 (最後のカンマがあった場所) にあるすべてを抽出します。

また、結果は元のデータとリンクしているため、元のデータを変更すると、結果も自動的に更新されます。

Flash Fillを使用したテキストの削除

Flash Fillは、Excel 2013で導入されたツールで、それ以降のすべてのバージョンで使用できます。

手動でデータを入力する際にパターンを識別し、それを外挿することで列全体のデータを得るという仕組みです。

つまり、特定の文字の前後のテキストを削除したい場合、結果がどのようになるか (手動で何度か入力して) フラッシュ フェアリーに示すだけで、フラッシュ フィルが自動的にパターンを理解して結果を返してくれるのです。

特定の文字以降のテキストを削除する必要のあるデータセット

Flash Fillを使用してこれを行うための手順は次のとおりです。

  1. データの隣の列であるセル B2 に、手動で「Jeffery Haggins」と入力します (これは期待される結果です)最初のセルに期待される結果を入力します
  2. セル B3 に、「Tim Scott」と入力します (これは期待される結果です)。 2つ目のセルに期待される結果を入力
  3. 範囲B2.B10を選択します。B10範囲全体を選択
  4. [ホーム]タブをクリック
  5. [編集]グループで[塗りつぶし]ドロップダウンオプションをクリック
  6. [フラッシュ塗りつぶし]をクリック[フラッシュ塗りつぶし]をクリック

以上の手順で、以下のような結果が得られます。

フラッシュ フィルの結果

結果の列（この例では B 列）のセルを選択した後、フラッシュ フィルのキーボード ショートカット Control + E を使用することもできます

フラッシュ フィルは素晴らしいツールであり、ほとんどの場合、パターンを認識して正しい結果を得ることができます。

なので、フラッシュ フィルの結果を必ず再確認してください

また、フラッシュ フィルを使用して特定の文字の後のテキストをすべて削除したように、同じ手順で特定の文字の前のテキストを削除することができます。

VBA を使用したテキストの削除 (カスタム関数)

特定の文字の前後にあるテキストを削除するという全体のコンセプトは、その文字の位置を見つけることに依存します。

この作業を頻繁に行う必要がある場合は、VBA (ユーザー定義関数と呼ばれる) を使用してカスタム関数を作成することで、このプロセスを簡略化することができます。

いったん作成した関数は、何度でも再利用することができます。

Function LastPosition(rCell As Range, rChar As String)'This function gives the last position of the specified character'This code has been developed by Sumit Bansal (https://trumpexcel.com)Dim rLen As IntegerrLen = Len(rCell)For i = rLen To 1 Step -1If Mid(rCell, i - 1, 1) = rChar ThenLastPosition = i - 1Exit FunctionEnd IfNext iEnd Function

このVBAコードを、VB Editorの通常のモジュールか、Personal Macro Workbookに配置する必要があります。 ワークブック内では、他の通常のワークシート関数と同様に使用することができます。

この関数は2つの引数を取ります：

  1. 特定の文字や文字列の最後の出現を見つけたいセルの参照
  2. 位置を見つけたい文字や文字列

以下のようなデータセットがあり、最後のコンマの後のテキストをすべて削除して、最後のコンマの前のテキストだけを表示したいとします。

以下はそのための数式です。 

=LEFT(A2,LastPosition(A2,",")-1)

VBA 特定の文字以降のテキストを削除するカスタム関数

上記の数式では、最後のコンマの位置を検索するように指定しています。

この式は、前のセクションで使用した長いテキスト式と比較して、はるかに短く、使いやすいことがお分かりいただけると思います。

以上が、Excelで特定の文字の前後のテキストをすばやく削除するために使用できるメールの一部です。

単純な1回限りのことであれば、検索と置換を使用して行うことができます。

このチュートリアルがお役に立ちましたら幸いです。

その他のExcelのチュートリアルはこちらです。

  • Formulas using a Cell (or Range of Cells) in Excel
  • How to Remove the First Character from a String in Excel
  • How to Remove Comma in
  • Excelで文字列から最初の単語を抽出する方法
  • Excelで名字と名前を分離する（数式を使った名前の分割）

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です