Excelで文字列から数字とテキストを分割する方法

投稿日: 2020年10月22日 2020年11月12日
タグ: excel-text-formulas

多くの場合、分析のためにフィールドとサーバーから混合データを取得します。このデータは通常ダーティで、列に数字とテキストが混在しています。分析前にデータクレンジングを行っている間、私は数字とテキストを別々の列に分けています。この記事では、その方法を説明します。

0034

シナリオ：

そこで、Exceltip.comの友人の1人が、コメントセクションでこの質問をしました。「Excelの数式を使用して、テキストの前とテキストの最後にある数字を区切るにはどうすればよいですか。たとえば、125EvenueStreetやLoveYou3000などです。」テキストを抽出するには、RIGHT、LEFT、MIDおよびその他のテキスト関数を使用します。抽出するテキストの数を知る必要があります。そして、ここで最初に同じことを行います。

数字が文字列の終わりにあるときに文字列から数字とテキストを抽出する上記の例では、このシートを用意しました。セルA2には、文字列があります。セルB2にはテキスト部分が必要で、C2には数値部分が必要です。

0035

したがって、番号が始まる位置を知る必要があります。次に、Leftおよびその他の関数を使用します。したがって、最初の数値の位置を取得するには、以下の一般式を使用します。

文字列の最初の数値の位置を取得するための一般式：

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String_Ref&"0123456789")

これにより、最初の数値の位置が返されます。

上記の例では、この数式を任意のセルに記述します。

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))

テキスト部分の抽出

見つかった最初の数値がテキストの15番目の位置にあるため、15が返されます。後で説明します。

ここで、テキストを取得するには、左から文字列から15〜1文字を取得する必要があります。したがって、 link：/ excel-text-extract-text-from-a-string-in-excel-using-excels-left-and-right-function [LEFT関数を使用してテキストを抽出します。]

左からテキストを抽出する式

=LEFT(A5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)

0036

ここでは、MIN（ link：/ text-excel-search-function [SEARCH]（\ {0,1,2,3,4,5,6,7,8,9）によって返される数値から1を引いただけです。 }、A5＆ “0123456789”））。

番号部分の抽出

数字を取得するには、最初に見つかった数字から数字を取得する必要があります。したがって、文字列の全長を計算し、最初に見つかった数値の位置を減算して、それに1を加算します。シンプル。ええ、それはただ複雑に聞こえます、それは単純です。

右から数値を抽出する式

=RIGHT(A5,LEN(A5)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))+1)

ここでは、 `link：/ len-functio [LEN function]を使用して文字列の全長を取得し、最初に見つかった数値の位置を減算してから、それに1を加算しました。これにより、合計数がわかります。 //excel-text/extract-text-from-a-string-in-excel-using-excels-left-and-right-function.html[ExcelのLEFTおよびRIGHT関数を使用したテキストの抽出] `の詳細については、こちらをご覧ください。

0037

したがって、LEFTおよびRIGHT関数の部分は単純です。トリッキーな部分は、最初に見つかった番号の位置を示すMINおよびSEARCH部分です。それを理解しましょう。

仕組み

LEFT関数とRIGHT関数がどのように機能するかを知っています。最初に見つかった数値の位置を取得するこの式の主要部分を調べます。つまり、

MIN（ link：/ text-excel-search-function [SEARCH]（\ {0,1,2,3,4,5,6,7,8,9}、String＆ “0123456789”）

`link：/ text-excel-search-function [SEARCH function]`は文字列内のテキストの位置を返します。

SEARCH（ ‘text’、 ‘string’）関数は2つの引数を取ります。最初はテキストです。検索したい、2番目に検索したい文字列。

ここでの検索では、テキスト位置に0から9までの数字の配列があります。文字列位置に「」と連結された文字列があります。 0123456789 “＆*演算子を使用します。なぜですか？説明します。

配列\ {0,1,2,3,4,5,6,7,8,9}の各要素は指定された文字列で検索され、配列内の同じインデックスにある配列形式の文字列でその位置を返します。
値が見つからない場合、エラーが発生します。したがって、すべての式でエラーが発生します。これを避けるために、テキスト内の数字「0123456789」を連結しました。これにより、常に文字列内の各数字が検出されます。

これらの数字は最後にあります。したがって、問題は発生しません。

MIN関数は、SEARCH関数によって返された配列から最小値を返すようになりました。この最小値は、文字列の最初の数値になります。このNUMBER関数とLEFTおよびRIGHT関数を使用して、テキストと文字列の部分を分割できます。

例を見てみましょう。 A5には、通りの名前と家番号を含む文字列があります。それらを異なるセルに分離する必要があります。

まず、文字列の最初の数字の位置を取得する方法を見てみましょう。

MIN（SEARCH（\ {0,1,2,3,4,5,6,7,8,9}、A5＆ “0123456789”））：これはMIN（SEARCH（\ {0,1,2、 3,4,5,6,7,8,9}、” Monta270123456789 *”））

ここで、説明したように、検索はMonta270123456789の配列\ {0,1,2,3,4,5,6,7,8,9}の各数値を検索し、その位置を配列形式で返します。返される配列は\ {8,9,6,11,12,13,14,7,16,17}になります。どうやって？

0は文字列で検索されます。 8位にあります。したがって、最初の要素は8です。元のテキストの長さはわずか7文字であることに注意してください。それを得る。 0はMonta27の一部ではありません。次の1は文字列で検索されますが、これも元の文字列の一部ではなく、位置9になります。

次の2つが検索されます。元の文字列の一部であるため、インデックスは6になります。

同様に、各要素はある位置にあります。

これで、この配列はMIN（\ {8,9,6,11,12,13,14,7,16,17}）として `link：/ statistics-excel-min-function [MINfunction]`に渡されます。 MINは、元のテキストで見つかった最初の数値の位置である6を返します。

そして、この後の話は非常に単純です。この数値は、LEFTおよびRIGHT関数を使用してテキストと数値を抽出するために使用します。

数値が文字列の先頭にある場合に文字列から数値とテキストを抽出する上記の例では、数値は文字列の末尾にありました。数字が最初にある場合、どのように数字とテキストを抽出しますか。

上記と同様の表を用意しました。最初は数字だけです。

0038

ここでは、別の手法を使用します。数字の長さ（ここでは2）を数え、文字列の左側からその文字数を抽出します。

したがって、メソッドは= LEFT（文字列、数値の数）です

文字数を数えるために、これは式です。

数を数える一般式：

=SUM(LEN(string)-LEN(SUBSTITUTE(string,{"0","1","2","3","4","5","6","7","8","9"},""))

ここで、** `link：/ excel-text-formulas-excel-substitute-function [SUBSTITUTE function]`は、見つかった各数値を「」（空白）に置き換えます。置換された数値が見つかり、新しい文字列が配列に追加される場合、それ以外の場合は元の文字列が配列に追加されます。このようにして、10個の文字列の配列ができます。

これで、LEN関数は、これらの文字列の配列内の文字の長さを返します。

次に、元の文字列の長さから、SUBSTITUTE関数によって返された各文字列の長さを減算します。これにより、再び配列が返されます。

これで、SUMはこれらすべての数値を追加します。これは文字列内の数値の数です。

文字列から数値部分を抽出する

文字列内の数値の長さがわかったので、この関数をLEFTに置き換えます。

私たちの弦はA11なので、

左から数値を抽出する式

=LEFT(A11,SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))))

0039

文字列からテキスト部分を抽出する

数字の数がわかっているので、文字列の全長からそれを差し引いて文字列のアルファベット番号を取得し、right関数を使用して文字列の右側からその文字数を抽出できます。

右からテキストを抽出する式

=RIGHT(A11,LEN(A2)-SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))))

0040

仕組み

両方の式の主要部分はSUM（LEN（A11）-LEN（SUBSTITUTE（A11、\ {“0″、 “1”、 “2”、 “3”、 “4”、 “5”、 “6”、 “7”、 “8”、 “9”}、 “”）））

数字の最初の出現を計算します。これを見つけて初めて、LEFT関数を使用してテキストと数字を分割することができます。それで、これを理解しましょう。

SUBSTITUTE（A11、\ {“0″、 “1”、 “2”、 “3”、 “4”、 “5”、 “6”、 “7”、 “8”、 “9”}、 ” “）：

この部分は、これらの数値を何も/空白（ “”）に置き換えた後、A11の文字列の配列を返します。 27Montaの場合、\ {“27Monta”、 “27Monta”、 “7Monta”、 “27Monta”、 “27Monta”、 “27Monta”、 “27Monta”、 “2Monta”、 “27Monta”、 “27Monta”}が返されます。

LEN（SUBSTITUTE（A11、\ {“0″、 “1”、 “2”、 “3”、 “4”、 “5”、 “6”、 “7”、 “8”、 “9”}、 ” “））：

これで、SUBSTITUTE部分はLEN関数でラップされます。これは、SUBSTITUTE関数によって返される配列内のテキストの長さを返します。結果として、\ {7,7,6,7,7,7,7,6,7,7}になります。

LEN（A11）-LEN（SUBSTITUTE（A11、\ {“0″、 “1”、 “2”、 “3”、 “4”、 “5”、 “6”、 “7”、 “8”、 ” 9 “}、” “））：

ここでは、実際の文字列の長さから、上記の部分から返された各数値を減算しています。元のテキストの長さは7です。したがって、\ {7-7,7-7,7-6、….}になります。最後に、\ {0,0,1,0,0,0,0,1,0,0}があります。

SUM（LEN（A11）-LEN（SUBSTITUTE（A11、\ {“0″、 “1”、 “2”、 “3”、 “4”、 “5”、 “6”、 “7”、 “8” 、 “9”}、 “”）））：

ここでは、SUMを使用して、関数の上記の部分によって返された配列を合計しました。

これにより、2が得られます。これは文字列内の数値の数です。

これを使用して、テキストと番号を抽出し、それらを異なるセルに分割できます。このメソッドは、数値が最初と最後の両方のタイプのテキストで機能します。左と右の機能を適切に利用する必要があります。

SplitNumText関数を使用して文字列から数値とテキストを分割する

上記のメソッドは少し複雑で、テキストと数字が混在している場合は役に立ちません。テキストと数字を分割するには、このユーザー定義関数を使用します。

0041

構文：

=SplitNumText(string, op)

文字列：分割する文字列。

Op：これはブール値です。テキスト部分を取得するには、0またはfalseを渡します。数値部分には、trueまたは0より大きい任意の数値を渡します。

たとえば、文字列がA20にある場合、

文字列から数値を抽出する式は次のとおりです：

=SplitNumText(A20,1)

そして

文字列からテキストを抽出する式は次のとおりです：

=SplitNumText(A20,0)

上記の式を機能させるには、VBAモジュールで以下のコードをコピーします。

Function SplitNumText(str As String, op As Boolean)

num = ""

txt = ""

For i = 1 To Len(str)

If IsNumeric(Mid(str, i, 1)) Then

num = num & Mid(str, i, 1)

Else

txt = txt & Mid(str, i, 1)

End If

Next i

If op = True Then

SplitNumText = num

Else

SplitNumText = txt

End If

End Function

このコードは、文字列の各文字が数字かどうかをチェックするだけです。数値の場合はnum変数に、それ以外の場合はtxt変数に格納されます。ユーザーがopにtrueを渡すと、numが返され、そうでない場合はtxtが返されます。

私の意見では、これは文字列から数値とテキストを分割するための最良の方法です。

必要に応じて、ここからワークブックをダウンロードできます。

そうですね、これらはテキストと数字を異なるセルに分割する方法です。以下のコメントセクションで疑問やより良い解決策があれば教えてください。男と交流するのはいつも楽しいです。

以下のリンクをクリックして、作業ファイルをダウンロードしてください：

`link：/wp-content-uploads-2019-11-Split-Number-and-Text-from-A-Cell.xls [__ Split Number and Text from A Cell]

link：/ mail-send-and-receive-in-vba-how-to-extract-domain-name-from-email-in-excel [ExcelでEメールからドメイン名を抽出する方法]

link：/ excel-text-formulas-split-numbers-and-text-from-string-in-excel [Excelでの文字列からの数値とテキストの分割]