ダンにはセルの列があり、各セルには都市、州、郵便番号の3つの項目が含まれています。 （住所行に表示されるように、3つすべてが1つのセルにあります。）郵便番号の中には5桁のものもあれば、9桁のものもあります。ダンは、2文字の州と5桁の郵便番号の両方を現在のデータの右側にある独自のセルに取り込む必要があります。ダンは、Text to Columnsツールを使用できることを知っていますが、複数単語の都市名とコンマを処理する必要があるため、多くの作業が必要になると感じています。ダンは仕方がないのですが、もっと簡単な公式のアプローチがあるかもしれないと思います。

推奨事項を作成するには、データについていくつかの仮定を行う必要があります。たとえば、すべてのデータが次の形式であると仮定します。

My Town, CA 98765-4321

ダッシュ以降の部分（郵便番号の末尾部分）はオプションですが、コンマの位置は静的であり、常に町の名前に従い、状態は常に2文字で構成されます。この場合、州の略語と郵便番号の最初の5桁を抽出する2つの式を簡単に考案できます。

=MID(A1,FIND(",",A1)+2,2)

=MID(A1,FIND(",",A1)+5,5)

両方の数式はコンマをキー入力します。それは都市と本当に欲しい2つのアイテムの間の区切り文字として機能します。データにコンマがない場合、または複数のコンマがある場合、数式は目的の情報を返しません。

数式は、データに余分なスペースがないことも前提としています。カンマの後、州と郵便番号の間に最大で1つのスペースがあります。もちろん、これは簡単に実施できます。検索と置換を使用して、ワークシートの任意の場所で2つのスペースを1つのスペースに置き換えるだけです。

もちろん、データがこのように構造化されている場合でも、Text toColumnsツールを使用して作業を行うことができます。ツールを実行し、コンマに基づいてデータを分割するだけです。これにより、都市が1つのセルに残り、州と郵便番号が次のセルにまとめられます。次に、テキストから列への変換を再度使用できます。今回は2番目のセル（都市名ではありません）で、スペースに基づいてコンテンツを分割します。

データがそれほど構造化されていない場合（おそらく、アドレスまたは余分なスペースに複数のコンマが含まれている場合）、まったく異なるアプローチが必要になります。これに対処するための基本的な手法では、データをトリミングして無関係なスペース（先頭、末尾、内部）を削除し、最後のスペースと最後から2番目のスペースの場所を決定します。

次の式を使用して、データの最後のスペースの直後として定義されている郵便番号の5桁を引き出すことができます。

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ", CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)

最後から2番目のスペースの直後にある2文字を引き出すことにより、2文字の状態の省略形を返すことができます。

=MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1), LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,2)

データがさらに構造化されていない場合（おそらく、すべてに2文字の州の略語がない住所（NJではなくN.J.）が含まれている場合）は、マクロを使用してデータを分割するのが最適です。

これは、VBAには、Excelの数式を使用して実行できるものよりもはるかに豊富なテキスト処理関数のセットがあるためです。次のマクロは、州または郵便番号のいずれかを返すことができるユーザー定義関数を作成します。

Function GetStateZIP(rstrAddress As String, iAction As Integer) As String     Dim arr As Variant     Dim sState As String     Dim sZIP As String     Dim J As Integer     Dim K As Integer

Application.Volatile     rstrAddress = Trim(rstrAddress)

If Len(rstrAddress) = 0 Then Exit Function

sState = "?"

sZIP = "?"

For J = Len(rstrAddress) To 1 Step -1         If Mid(rstrAddress, J, 1) = " " And sZIP = "?" Then             sZIP = Mid(rstrAddress, J + 1, 5)

rstrAddress = Trim(Left(rstrAddress, J))

For K = Len(rstrAddress) To 1 Step -1                 If Mid(rstrAddress, K, 1) = " " And sState = "?" Then                     sState = Mid(rstrAddress, K + 1, 20)

rstrAddress = Trim(Left(rstrAddress, K))

End If             Next K         End If     Next J     If iAction = 1 Then         GetStateZIP = sState     End If     If iAction = 2 Then         GetStateZIP = sZIP     End If End Function

この関数を使用するには、セル参照と1（状態が必要な場合）または2（郵便番号が必要な場合）のいずれかを指定するだけです。セルA1にある住所の郵便番号を要求する例を次に示します。

=GetStateZIP(A1,2)

注：

このページ（または_ExcelTips_サイトの他のページ）で説明されているマクロの使用方法を知りたい場合は、役立つ情報を含む特別なページを用意しました。

linkここをクリックして、新しいブラウザタブでその特別なページを開きます。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント（9598）は、Microsoft Excel 97、2000、2002、および2003に適用されます。Excel（Excel 2007以降）のリボンインターフェイス用のこのヒントのバージョンは、次の場所にあります。

link州と郵便番号の抽出。