Excel からセルに住所を抽出するにはどうすればいいですか?

Question 1

「Anchorage, AK 99508 US」というテキストが A1 にあると仮定して、次の数式を入力します。

A4 --> =MID(A1,1,(FIND(",",A1,1))-1)
B4 --> =MID($A$1,(FIND(" ",$A$1,A3))+1,C3-(FIND(" ",$A$1,A3))-1)
C4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))-(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))-1)
D4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))+1,LEN(A1))

key : find() は、mid() の開始値と終了値を識別するために使用されました。ここでは、A1 テキスト内の各 "," および " " を検索します。

Answer

「Anchorage, AK 99508 US」というテキストが A1 にあると仮定して、次の数式を入力します。

A4 --> =MID(A1,1,(FIND(",",A1,1))-1)
B4 --> =MID($A$1,(FIND(" ",$A$1,A3))+1,C3-(FIND(" ",$A$1,A3))-1)
C4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))-(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))-1)
D4 --> =MID($A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,(FIND(" ",$A$1,FIND(",",A1,1)))+1))+1))+1,LEN(A1))

key : find() は、mid() の開始値と終了値を識別するために使用されました。ここでは、A1 テキスト内の各 "," および " " を検索します。

Question 2

あるいは... かなり異なるアプローチを取ることもできます。実際には 2 つあります。

1 つは、テクニックを使うことですFILTERXML()。まず、データを「残りの部分」と最後の行の 2 つに分割します。「残りの部分」は最初の出力セルに入ります。(以下に、分割するかなり簡単な方法を示します。) 最後の行は、文字列として処理され、カンマとスペースが HTML タグ ( </Something><Something>) に置き換えられ、文字列が HTML 文字列に半分変換され、開始タグが先頭に追加され、終了タグが末尾に追加され、さらにこれらすべてを囲む外側の HTML タグが追加されます。これが関数への実際の入力でありFILTERXML()、その出力は最後の行の情報を保持する 4 つのセルです。

これは思ったより簡単で、数回使用すれば、近い時間であれば、型にはまったやり方になります。主な作業は、データを分割して準備することです。これは、新しい使用ごとに、データが異なる方法でパッケージ化されているように見えるためです。

練習後のクッキーカッターの性質に加えて、{CSE}エントリ ( Ctrl-Shift-Enter) を必要とせずに「自然に」配列出力を生成するという利点があります。もう 1 つの利点は、出力要素を番号または[Last](アドレス指定のこの要素は文字列テクニックを使用して構築できます) を使用して選択できるため、指定できる場合は正確な位置データを抽出できることです。

Excel 4 Macro別のアプローチは、と呼ばれる ("E4M") コマンドを使用することですEVALUATE()。これをセルの数式で直接使用することはできませんが、これを保持するための名前付き範囲を作成する必要があります。セルに提示するものを作成することも、すべてを 1 つの数式にまとめて、それを名前付き範囲の関数内に配置することもできます。自分にとって最も適したもの、または自分の好みに最も合ったものを選んでください。

いずれの場合も、の場合と同様に、FILTERXML()入力を準備する必要があります。ここでの違いは、この方法で行うことは非常に馴染みのあることであり、カンマを忘れたりタイプミスをしたりといった小さな間違いが「自然」に見え、比較的簡単に見つけられることです。ではFILTERXML()すべてが「間違っている」ように見え、しばらくの間、出力がどのようになるべきかについて慣れていないため、エラーが発生しやすく、見つけにくくなります。の利点は、ですEVALUATE()。

では、データをどのように準備すればよいのでしょうか。2 つのチャンクが必要です。抽出して表示する最初の部分 (上記で「残りの部分」と呼んでいます) と、特別な方法で処理する部分です。最後の部分では、Excel が数式で内部的に生成する配列のような文字列を作成します。行全体に表示される配列は次のようになります。{"a","b",1,"K"}列全体に表示される同じデータは次のようになります{"a";"b";1;"K"}。もちろん、これらを組み合わせて、列と行の範囲にわたって表示することもできます。後者のように、区切り文字としてセミコロンを使用して作成します。

しかし、最後の行には区切り文字が 1 つもありません。「,」と「」があります。そこで、まずその部分をに入れて、SUBSTITUTE()「,」（「カンマとスペース」）をスペースだけに変更します。これで区切り文字が 1 つだけになったので、を使って、必要な「;」に変更できます。また、だけでなくSUBSTITUTE()を使用して、すべての要素を囲む必要な二重引用符のほとんどを適用することもできます。実際には、Excel では二重引用符を数式要素として使用するとかなり厄介になるため、を使用する方がはるかに簡単です。「ほとんどの」というのは、開始と終了の二重引用符は要素の間にのみ追加でき、開始と終了の二重引用符は必要なままだからです。そのため、適切な中括弧とともに、開始文字列と終了文字列にそれらを追加します。これで、分解できる適切な文字列ができました。";";CHAR(34)SUBSTIITUTE()EVALUATE()

これをセルに作成し、うまくいったら、名前付き範囲を作成し、すべてをEVALUATE()の括弧内に配置します。次の数式でその様子を確認できます。入力データに対して出力がどのように配置されるかは明確ではないため、1 つの入力の下の 2 つの行を使用しました。つまり、A1 に入力し、A2 と A3 に出力します。必要に応じて調整できます。名前付き範囲内で、次のようになります。

=EVALUATE("{"&CHAR(34)&SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND(CHAR(10),A1,SEQUENCE(1,LEN(A1))),""))),", "," ")," ",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}")

SEQUENCE()質問が書かれた時点では、利用可能だったかもしれません。そうでなかったかもしれません。結局のところ、私は、問題を検索しているが最新の解決策を求めている人々のために書いているので、簡潔さのためにそれを使用しました。ただし、当時はそれに代わるものが存在し、一般的なものはROW(1:xxx)単純または複雑な方法を使用していました。

「その日」には、{CSE}エントリーと一緒に入力する必要があったかもしれません。

すでに説明したすべてのことを実行します。

ここでの小さな要素は、入力をどのように分割するかです。問題はそれぞれ異なりますが、あなたの問題はそれほど難しくはありません。入力セルの各行はAlt-Enter、、またはで終わりますCHAR(10)。したがって、「コンテンツの長さから、コンテンツSUBSTITUTE()を削除した後のコンテンツの長さを引いた長さ」という手法を使用して、それらをカウントできます。次に、通常は行われないことを行うをラップしますFIND()。つまり、「位置番号」パラメーター (3 番目) を使用して、1 から入力の長さまでの値の配列を取得し、FIND()入力の 1 つ先を調べるの配列を作成します。これにより、入力を通過するときに次の出現の値の配列が生成されます。最後の 1 つ以降に内容があるため、最後にエラーがあります。これらのエラーは使用できないため、IFERROR()空白にします。見つかった最大数が入力のブレークポイントであるため、を使用してMAX()それを出力します。次に、その数の文字のを 2 つの数式で実行してLEFT()、すべての簡単な最初の数行 (3、4、5 など、いくつでもかまいません) をその場でダンプするだけです。次に、RIGHT()入力の長さからこのブレークポイントを引いた入力の残りを収集します。これが関数の入力ですEVALUATE()。

の場合と同様にFILTERXML()、素材のマッサージはどの方法でも行う必要があるため、これはすべて実は非常に簡単です。その後、にポンと差し込むだけですEVALUATE()。

Answer