Excel セルの文字列から最後の 7 つの数値 (文字ではない) を抽出する必要があります。文字列にはファイル名 (文字、数字、ダッシュの組み合わせ) と、別の列に移動する必要がある 7 つの数字が含まれます。
例えばJohnson Set1- 0 0 1 14 9 54 0
数値は 0 から 999 まで変化するため、最後の 7 文字だけを抽出することはできません。
答え1
つまり、あなたが望むものを手に入れるために使えるトリックがいくつかあります。ここでは、それらをできるだけ詳しく説明し、それらのトリックを使ってあなたが望むものを手に入れるための公式を紹介します。
重要なのは、数字のリストが始まる前のスペースを見つけることです。例が十分に一般的なものであれば、このスペースは常に右から 7 番目のスペースになります。問題は、ファイル名にスペースの数が決められているという規則がないように思われるため、右から 7 番目が左から任意の数字のスペースになる可能性があることです。また、このSUBSTITUTE関数を使用すると、文字列のどのインスタンス (左から) を置き換えるかを指定できます。
右から 7 番目のスペースを取得するには、それを左からのスペースに変換する必要があります。これを行うには、文字列全体のスペースの数を知る必要があります。それがわかれば、あとは減算するだけです。
SUBSTITUTE文字列内のスペースの合計数を調べるには、 関数と関数を使用する方法がありますLEN。その方法は、文字列内のすべてのスペースを何もないものに置き換え、出力の長さを元の文字列の長さと比較することです。違いは、もちろん元の文字列内のスペースの数です。したがって、 内の文字列の場合A1、次の式はスペースの数を返します。
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
したがって、必要な左からのスペースのインスタンスの左側には常に 6 つのスペースが追加されます。
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6
さて、次の問題は、SUBSTITUTEでは変更したいインスタンス番号しか指定できないことです。FINDは、番号を抽出するのに最も便利ですが、この機能はありません。そこでの秘訣は、 を使って、必要に応じSUBSTITUTEて使用できる文字列を生成することですFIND。右から 7 番目のスペースを、文字列の他の場所には存在しない文字列に置き換えると、検索する一意の部分文字列が得られます。したがって、たとえば、次のコードを使用して、目的のスペースを "REPLACE!ME" に置き換えることができます。
=SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6)
ここで、文字列内の「REPLACE!ME」のインデックスを見つけて、その右側にあるすべてのものを返す必要があります。これはRIGHT、、、LENおよびFIND関数を使用して実行できます。最後の関数は多少繰り返しになりますが、目的は達成されます。
=RIGHT(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6),LEN(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-FIND("REPLACE!ME",SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-LEN("REPLACE!ME")+1)