Excelで英数字の文字列から数値を削除する方法
「link:/ excel-text-formulas-split-numbers-and-text-from-string-in-excel [Excel2016以前のセルから数値を削除する]」方法を学習しました。私たちが使用した数式は少し複雑でしたが、Excel 2019と365は新しいおもちゃ、つまり関数でゲームに登場しています。 Excel 2019および365では、いくつかの新しい関数(TEXTJOINおよびSEQUENCE)が導入されています
これにより、数字を削除し、新しいセルで数値以外の値のみを取得する作業が簡単になります。これを行うのに役立つ数式を、より便利に使用します。 ジェネリックフォーミュラ
= |
Jumbled_text:これは、すべての数値を抽出するソーステキストです。
NumChars:これは処理する文字の総数です。
jumbled_textには、この数値(文字と数値の組み合わせ)を超える文字を含めることはできません。
物事を明確にするための例を見てみましょう。
例:数字を削除し、すべてのアルファベットと非数字を抽出します。
だからここにいくつかの英数字のテキストがあります。このテキストには、いくつかの数字といくつかの非数字が含まれています。 D列で、数字を削除し、アルファベットとその他の文字値のみを取得する必要があります。
乱雑なテキストの合計文字数が100を超えるとは思わないので、ここではNumCharsの値は100です。必要に応じて、この数を増減できます。
上記の一般式をここに適用して、数字を取り除きます。
= |
エンターボタンを押すと、すべての数字が削除され、アルファベット値のみが残ります。この数式を下にドラッグして、列Bのすべてのセルから文字列から数値を削除します。
どのように機能しますか?
まず、この式がどのように解決されるかを段階的に見ていきましょう。
1→
|
2→
is…site”,\{1,2,3,…100},1)+0), |
3→ |
4→
|
5→
|
6→
|
7→ “This is number one website” |
読みやすくするために、配列全体を記述していません。長い配列を示すためにドット(…)を使用しました。
ご覧のとおり、式は内側から解き始めます。最初に、 `link:/ excel-365-functions-the-sequence-function-in-excel [SEQUENCEfunction]`が解決されます。文字数として100を超えたので。 1から100までの数値の配列を返します。
この配列は、開始番号としてMID関数に提供されます。 mid関数は、文字列内の各インデックスに移動し、配列内の各文字を分割します。手順3で確認できます。スペースがかかりすぎるため、配列全体は表示していません。 \ {“T”; “h”; “i”; “s”; “”、 “1” … “”; “”} *次に、各文字に0を追加します。 Excelでは、数字以外の文字に数字を追加しようとすると、 `link:/ Tips-value-error-and-how-to-fix-it-in-excel [#VALUE!
エラー] `。したがって、数値の配列と#VALUEを取得します。エラー。
\ {#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}
この配列は、 link:/ excel-formula-and-function-iserror-function [ISERROR]
に提供されます。関数。ご存知のように、ISERROR関数は、エラーの場合はTRUEを返し、エラー以外の値の場合はFALSEを返します。したがって、TRUEとFALSEの配列を取得します。
数字以外の文字の場合はTRUE、数字の場合はFALSE。
\ {TRUE; TRUE; TRUE; TRUE; FALSE…; TRUE}。
IFのTRUEセクションステートメントにも同じことが起こります( link:/ excel-text-formulas-the-mid-function-in-excel [_MID _]
(B3、 `link:/ excel-365-functions-the -sequence-function-in-excel [_SEQUENCE _] `(100)、1))。
B3の英数字文字列のすべての文字の配列を返します。
これで、TRUEステートメントごとに、IF関数はtrueセクションの配列から対応する文字を返します。 FALSEの場合、IFは空白( “”)を返します。これで、数字を含まない配列ができました。 \{“この”;””….;””}。最後に、この配列は link:/ excel-365-functions-the-excel-textjoin-function [TEXTJOIN *]
によって提供されます。関数。この関数は、指定された区切り文字を使用して、空白の値を無視して、指定されたテキストを相互に結合します。したがって、数字を含まない文字列を取得します。これはナンバーワンのウェブサイト*です。
式の強化
上記の式は、文字数を処理するためにハードコードされた数値を使用しています(100を使用しました)。しかし、あなたはそれを動的にしたいかもしれません。その場合、あなたはそれを正しく推測しました、あなたはLEN関数を使うことができます。処理には正確な文字数が必要です。したがって、式は次のようになります。
= |
ここで、LEN関数は、英数字ストリング内の正確な数字を自動的に検出します。これにより、最大文字数を決定する負担が軽減されます。
SEQUENCE関数の代替
SEQUENCE関数を使用したくない場合は、ROW関数とINDIRECT関数を組み合わせて `link:/ information-formulas-how-to-get-sequential-row-number-in-excel [連続番号の生成]を使用できます。 `。
= |
INDIRECTはテキスト(「1:100」)を実際の範囲に変換し、次にROW関数は1から100までのすべての行番号をリストします(100は単なる例です。任意の番号にすることができます)。
そうですね、これがExcelの英数字の文字列から数字以外の文字を取り除く方法です。私が十分に説明し、この記事があなたを助けたことを願っています。このトピックまたは他のExcel / VBAトピックに関して質問がある場合。それからExcellingを続けるまで。
関連記事:
link:/ excel-text-formulas-how-to-remove-non-numeric-characters-from-cells-in-excel [Excel 2019でセルから非数字を削除する方法:]`から非数字を削除するにはExcelの英数字文字列では、新しいTEXTJOIN関数を使用します。文字列から数字以外の文字を削除すると、データをクリーンアップしてデータ分析を改善するのに役立ちます。だから、これがあなたのやり方です `link:/ excel-text-formulas-split-numbers-and-text-from-string-in-excel [Split Numbers and Text from String in Excel 2016 and Older]
:私たちがしなかったときTEXTJOIN関数があり、LEFT関数とRIGHT関数を他のいくつかの関数と一緒に使用して、文字列から数値文字と非数値文字を分割しました。 link:/ excel-text-extract-text-from-a-string-in-excel-using-excels-left-and-right-function [ExcelのLEFTおよびRIGHT関数を使用してExcelの文字列からテキストを抽出する]
:文字列からExcelのテキストを削除するには、ExcelのLEFTおよびRIGHT関数を使用できます。これらの関数は、文字列を動的に切り刻むのに役立ちます。\ link:/ excel-text-formulas-remove-leading-and-trailing-spaces-from-text-in-excel [Excelのテキストから先頭と末尾のスペースを削除する]
:*先頭と末尾のスペースは視覚的に認識しにくく、データを台無しにする可能性があります。文字列からこれらの文字を削除することは、データクリーニングの基本的かつ最も重要なタスクです。 Excelで簡単に行う方法は次のとおりです。
link:/ excel-text-formulas-remove-characters-from-right [右から文字を削除]
:* Excelで文字列の右から文字を削除するには、LEFT関数を使用します。はい、LEFT機能です。 LEFT関数は、指定された数の文字をLEFTから保持し、その右側からすべてを削除します。
link:/ excel-text-formulas-remove-unwanted-characters-in-excel [Excelで不要な文字を削除する]
:* Excelで文字列から不要な文字を削除するには、SUBSTITUTE関数を使用します。 SUBSTITUTE関数は、指定された文字を別の指定された文字に置き換え、新しい変更された文字列を生成します。
link:/ excel-text-formulas-how-to-remove-text-in-excel-starting-from-a-position [Excelの位置から始まるExcelのテキストを削除する方法]
:*からテキストを削除するには文字列の開始位置には、ExcelのREPLACE関数を使用します。この関数は、削除する開始位置と文字数を決定するのに役立ちます。
人気の記事:
link:/ keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [生産性を高めるための50のExcelショートカット]
|あなたの仕事をより速くしてください。これらの50のショートカットにより、Excelでの作業がさらに高速になります。
ExcelVLOOKUP関数の使用方法 |これは、さまざまな範囲やシートから値を検索するために使用される、Excelの最も使用され人気のある関数の1つです。 link使用方法
link:/ forms-and-functions-introduction-of-vlookup-function [Excel]
COUNTIF関数 |この驚くべき関数を使用して、条件付きの値をカウントします。
特定の値をカウントするためにデータをフィルタリングする必要はありません。ダッシュボードを準備するには、Countif関数が不可欠です。
link:/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法]
|これは、ダッシュボードのもう1つの重要な機能です。これは、特定の条件で値を合計するのに役立ちます。