数式を利用せず、「区切り位置」ツールを利用して区切り文字で文字列を分割してセルに配置する方法もあります。
「区切り位置」ツールを利用する方法はこちらの記事を参照してください。
また、VBAマクロのSplit関数を利用する方法はこちらの記事を参照してください。
Excelで数式を利用して文字列を区切り文字で分割する方法を紹介します。
Excelで文字列を区切り文字で分割したいことがあります。C#などのプログラムではstring.split()メソッドなど便利な関数が用意されていますが、
Excelではsplitメソッドと同様の関数は用意されていません。また、正規表現もマクロを利用しない限りは利用できません。
この記事では、Excelの標準の関数を利用して、区切り文字で文字列を分割する手順を紹介します。
スピルが利用できるExcelではTEXTSPLIT関数で文字列分割ができます。
指定した区切り文字で分割しスピルで値を表示する場合は、TEXTSPLIT 関数の次の書式を利用します。
指定した区切り文字から左側の文字列を取り出す場合は、LEFT関数と、FIND関数を利用します。
以下の数式で、指定した区切り文字より左の文字列を取得できます。
指定した区切り文字から右側の文字列を取り出す場合は、RIGHT関数と、FIND関数を利用します。
以下の数式で、指定した区切り文字より左の文字列を取得できます。
Excelのシートを作成し、B2セルに次の値を入力します。
penguin,duck,camel,owl,bear
B3セルに次の数式を入力します。
=TEXTSPLIT(B2,",")
数式の入力を確定します。B3セルに"penguin", C3セルに"duck", D3セルに"camel", E3セルに"owl", F3セルに"bear"が表示されます。
TRANSPOSE()関数を利用します。
TRANSPOSE関数の詳細はこちらの記事を参照してください。
B3セルの数式を以下の数式に変更します。
=TRANSPOSE(TEXTSPLIT(B2,","))
下図のExcel表を準備します。
セルの文字列は下記のとおりです。","で2つの数値が区切られた文字列です。
| 32,45 |
| 11,5 |
| 82,34 |
| 6,9 |
| 12,8 |
| 22,45 |
C2セルに下記の数式を入力します。
LEFT関数を用いてB2のセルの先頭(左側)から指定した文字列を抜き出します。何文字抜き出すかが FIND(",",B2) の部分になります。FIND関数を用いて","までの文字数を取得します。FINDの戻り値をそのまま利用すると、","を含んだ文字列が取得されてしまうため、1を引いて、","の手前までの文字列を抜き出します。
数式の入力を確定します。","の手前までの文字列である"32"が取り出せました。
C2セルの数式をコピーしC列の他のセルにペーストします。他のセルでも同様に、","の手前までの文字列がC列のセルに表示されます。
","の手前までの文字列を取得できました。
下図のExcel表を準備します。
セルの文字列は下記のとおりです。","で2つの数値が区切られた文字列です。
| 32,45 |
| 11,5 |
| 82,34 |
| 6,9 |
| 12,8 |
| 22,45 |
C2セルに下記の数式を入力します。
RIGHT関数を用いてB2のセルの末尾(右側)から指定した文字列を抜き出します。何文字抜き出すかが LEN(B2)-FIND(",",B2) の部分になります。FIND関数を用いて","までの文字数を取得します。取得された数値は先頭からの文字数となりますが、RIGHT関数には末尾から取得する文字数を与えるため、全体の長さからFINDで取得した位置を引く必要があります。文字列全体の長さはLEN(B2)で取得し、全体の長さから","の位置までを引くことにより、末尾からの文字数を求めます。
数式の入力を確定します。B2セルの文字列の ","より右側の文字列である"45"が抜き出されてC2セルに表示されます。
C2セルの数式をコピーしC列の他のセルにペーストします。他のセルでも同様に、","の右側の文字列がC列のセルに表示されます。
指定した区切り文字の右側と左側の文字列を取り出すことで、区切り文字で分割した文字列を取得できます。(下図のC列は","の左側の文字列を取り出す数式を記述し、D列は","の右側の文字列を取り出す数式を記述しています)
","で区切られたそれぞれの文字列が取得できています。
ただし、上記の文字は左寄せになって表示されていることから数値の文字列であっても文字列の形式としてセルに表示されています。この状態では数値として計算することはできないため、数値に変換する必要があります。
数値に変換する場合は、セルをコピーして値としてペーストします。
変換するセルを選択し、選択領域左上に表示される[!]アイコンをクリックします。下図のドロップダウンメニューが表示されますので、メニューの[数値に変換する]をクリックします。
選択したセルの値が文字列から数値に変換されます。表示形式も右揃えになり、数値として認識されていることが確認できます。
文字列を数値に変換する手順の詳細はこちらの記事を参照してください。
