マーティは、列Aに長い一連の部品番号が記載されたワークシートを持っています。

これらは、A123BC、AB123Cなどの文字と数字で構成されます。マーティはデータを3つの列に分割して、数字の前のテキストが1つの列に、数字が2番目の列に、数字の後のテキストが第3。

部品番号をセグメントに分割することを複雑にする要因は、結合された部品番号の各コンポーネントに設定された長さがないことです。コンポーネントが標準の長さである場合は、Excelの[テキストから列へ]機能を使用できます。それらはそうではなく、コンポーネント間に区切り文字がないため、ソリューションの潜在的な道は閉じられます。

数式を使用して部品番号を分解する場合は、抽出するコンポーネントごとに1つずつ、合計3つ必要になります。

部品番号が示されたパターン（テキスト、数字、テキスト）に従い、最初の部品番号がセルA1にあると仮定すると、セルB1で次を使用できます。

=LEFT(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0)-1)

これは配列数式として入力する必要があります。つまり、Ctrl + Shift + Enterを使用して入力する必要があります。数式は、部品番号の最初の数字を見つけて、その数字より前のすべてを返します。

長さが100文字を超えない部品番号で機能します。

部品番号の2番目のコンポーネントを抽出するには、セルC1に次の数式を入力します。

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)))

繰り返しますが、これは単一の数式であり、元の部品番号の各文字で機能するように、配列数式として入力する必要があります（Ctrl + Shift + Enter）。部品番号を調べて数字の始点を決定し、それらすべての数字を抽出します。文字列が数字で構成されている場合でも、テキスト文字列を返します。実際に数値として処理する場合（もちろん、先行ゼロを削除します）、次に示すように、数式全体をValue関数で囲む必要があります。

=VALUE(MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1))))

部品番号の最後のコンポーネントを取得するには、次の式を使用する必要があります。これも配列式として入力します。

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0)+COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)),100)

このアプローチは非常にうまく機能しますが、特にワークシートに多くの数式がある場合、配列数式は計算量が多いことで有名です。 1000個の部品番号を分解する必要がある場合、それは3,000個の配列数式になってしまうことを意味します。これは、再計算に非常に時間がかかる可能性があります。

このような状況に陥った場合は、マクロを使用して実際に部品番号を分解することをお勧めします。次のマクロは、すでに説明したように、テキスト、数字、テキストのパターンに従う部品番号で機能するはずです。

Sub Split1()

Dim C As Range     Dim sNew As New     Dim i As Integer          For Each C In Selection         sNew = ""

i = 1

' Get first part, which is text         Do While IsNumeric(Mid(C, i, 1)) = False             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 1).Value = sNew

' Pull next part, which should be digits         sNew = ""

Do While IsNumeric(Mid(C, i, 1)) = True             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 2).Value = sNew

' Use rest of original cell         sNew = Mid(C, i, Len(C))

C.Offset(0, 3).Value = sNew     Next C End Sub

マクロを使用するには、部品番号を選択して実行するだけです。このマクロは、セル内の文字列内の数値と非数値の間の変化を探すという概念を使用しています。これらの境界の1つに達すると、境界の前の元の文字列の一部が新しいセルに詰め込まれます。この概念は、次の例で行うように、少し短くすることができます。

Sub Split2()

Dim C As Range     Dim j As Integer     Dim k As Integer

For Each C In Selection         j = 1         Do While Not (IsNumeric(Mid(C.Value, j, 1))) And j <= Len(C)

j = j + 1         Loop         k = j

Do While IsNumeric(Mid(C.Value, k, 1)) And k <= Len(C)

k = k + 1         Loop

C.Offset(0, 1) = Left(C, j - 1)

C.Offset(0, 2) = Mid(C, j, k - j)

C.Offset(0, 3) = Mid(C, k, Len(C) - (k - 1))

Next C End Sub

もちろん、このバージョンのマクロと前のバージョンのマクロの違いは、このバージョンが元のセルをステップスルーし、境界を一度に決定することです。それらがわかっている場合は、元の部品番号のコンポーネントがセルに詰め込まれます。

部品番号を分解するための興味深いアプローチは、コンポーネント間の境界がどこにあるかを決定するいくつかの短いユーザー定義関数を使用することです。次の2つの関数について考えてみます。

Function pNumber(X)

i = 1     Do Until Mid(X, i, 1) Like "#": i = i + 1: Loop     pNumber = i End Function
Function pAlpha(X)

X = UCase(X)

i = pNumber(X)

Do Until Mid(X, i, 1) Like "[A-Z]": i = i + 1: Loop     pAlpha = i End Function

これらは前のマクロよりもはるかに短く、数値が始まる境界（pNumberの場合）とテキストの2番目のグループが始まる境界（pAlphaの場合）を返すだけです。関数を使用するには、次の3つの式を使用して、元の部品番号の1番目、2番目、および3番目のコンポーネントをそれぞれ返します。

=MID(A1,1,pNumber(A1)-1)

=MID(A1,pNumber(A1),pAlpha(A1)-pNumber(A1))

=MID(A1,pAlpha(A1),LEN(A1)-pAlpha(A1)+1)

注：

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

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

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

このヒント（10369）は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice 365のExcelに適用されます。Excelの古いメニューインターフェイス用のこのヒントのバージョンは、次の場所にあります。

link可変長部品番号の分割。