☰

Excel でテキストを列に分割する機能

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 spreadsheet
Excel でテキストを列に分割する機能

以下に示すようなデータ行を含む Excel スプレッドシートがあります。製品名のコードと番号があります。

QA32 Product One (Large) 23
AZ63 Product Two 96
PR65 Product Three 149

各フィールドを列に分割するにはどうすればよいですか?

Excel で区切りオプションを使用してスペースで区切って「テキストを列に分割」してみましたが、一部の製品名に複数の単語が含まれていたため、一部の数字が間違った列に表示されました。例:

Code     Name       Number       
QA32    Product      One       (Large)    23
AZ63    Product      Two         96
PR65    Product      Three       149

望ましい結果は次のとおりです。

Code        Name                Number
QA32    Product One (Large)       23
AZ63    Product Two               96
PR65    Product Three             149

ご協力いただければ幸いです!

答え1

を使用してFIND-function、最初のスペースと最後のスペースの位置を決定します。その後、 を使用してMID-functionデータを 3 つの部分に分割します。スペースの位置がわかっていれば、分割は簡単です。 は左から右に進むため、最初のスペースを見つけるのも簡単ですFIND。残念ながら、Excel に右から左に検索するように指示する方法はありません。

難しいのは、最後のスペースを特定することです。ここでは配列数式を使用します。
配列数式を入力するには、数式をセルに貼り付けて を押さずに、Enterの代わりに+ +
を押します。CtrlShiftEnter

{=MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A))}

正常に完了すると、Excel は開始と終了の中括弧を追加します。

指定されたセルにすべての数式を挿入し、autofill down

A2: ここにデータを入力します
B2: =FIND(" ",A2,1)
C2: =MAX((MID(A2,ROW(A:A),1)=" ")*ROW(A:A)) <-配列数式!
D2: =MID(A2,1,B2-1)
E2: =MID(A2,B2+1,C2-B2-1)
F2:=MID(A2,C2+1,LEN(A2))

結果画面
ここに画像の説明を入力してください

答え2

適切なアルゴリズムが返す値:

  • コード = 最初の単語
  • 製品 = 最初の単語と最後の単語の間にあるすべて
  • 数字 = 最後の単語

その場合、次の数式が機能するはずです。

データが A1 にあると仮定します。

Code:     =LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)
Product:  =TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),100,LEN(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)))-198))
Number:   =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

関連情報