S002｜区切り文字で文字列を縦並びに分割する方法｜Excelのデータ抽出方法

区切り文字で縦並びに分割するには、
　・Microsoft365はTEXTSPLIT関数
　・Excel2021/2019/2016はSUBSTITUTE関数
で区切り文字を空白に置き換えてから分割しましょう。

例題１｜文字列を［,］(コンマ)区切りで縦並びに分割する

1. 抽出するセル（B3セル）を選択し、「=TEXTSPLIT(」と入力する。
2. ［文字列］…A1セルを選択する。
3. ［列区切り］…省略。
4. ［行区切り］…区切り文字の［,］を入力する。
5. ［空文字の無視］…省略。
6. ［一致モード］…省略。
7. ［空の場合の値］…省略して、「Enter」キーで数式を確定する。

【数式】=TEXTSPLIT(A1,,”,”)

数式の解説

「=TEXTSPLIT(A1,,“,”)」の数式は、A1セルの文字列を［,］で区切って縦並びに分割する

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx  

例題２｜文字列を［,］(コンマ)区切りで縦並びに分割する

1. 抽出するセル（B3セル）を選択し、「=TRIM(」と入力する。
2. ［文字列］…「MID(」と入力する。
3. MID関数の［文字列］…「SUBSTITUTE($A$1,“,”,REPT(“ ”,100))」と入力する。
4. ［開始位置］…「ROW(A1)*100-99」と入力する。
5. ［文字数］…「100」を入力して、「Enter」キーで数式を確定する。
6. 数式を必要な分だけ複写する。

【数式】=TRIM(MID(SUBSTITUTE($A$1,”,”,REPT(” “,100)),ROW(A1)*100-99,100))

数式の解説

「SUBSTITUTE($A$1,“,”,REPT(“ ”,100))」の数式は、区切り文字の［,］を［”
“］(半角スペース)100個に置き換える。
置き換え後の文字列をTRIM関数の引数の［文字列］に指定して「MID(SUBSTITUTE($A$1,”,“,REPT(”
“,100)),ROW(A2)*100-99,100)」と数式を作成すると、1文字目から100文字の「A001(半角スペース96個)」が抽出される。
この時、余分な半角スペースが文字列の末尾に残るので、TRIM関数で削除する数式を作成する。
結果、区切り文字の［,］までの「会員ID」が抽出される。この数式を縦方向にコピーすることで、区切り文字の［,］ごとに分割して抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx  

応用１｜

Microsoft 365なら、TEXTSPLIT関数1つで、区切り文字で行列分割や、複数の種類の区切り文字すべての分割が可能です。
例えば、［,］で横並び、［;］で縦並びに分割するなら、
❶引数［列区切り］に［,］、❷［行区切り］に［;］を入力して数式を作成しましょう。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-application1.xlsx