iPentec.com / Doc / Excel / Microsoft Office

数式を利用してセルの文字列を区切り文字で分割する - Excel

Excelで数式を利用して文字列を区切り文字で分割する方法を紹介します。

補足

数式を利用せず、「区切り位置」ツールを利用して区切り文字で文字列を分割してセルに配置する方法もあります。「区切り位置」ツールを利用する方法はこちらの記事を参照してください。
また、VBAマクロのSplit関数を利用する方法はこちらの記事を参照してください。

概要

Excelで文字列を区切り文字で分割したいことがあります。C#などのプログラムではstring.split()メソッドなど便利な関数が用意されていますが、 Excelではsplitメソッドと同様の関数は用意されていません。また、正規表現もマクロを利用しない限りは利用できません。この記事では、Excelの標準の関数を利用して、区切り文字で文字列を分割する手順を紹介します。

書式

TEXTSPLIT 関数を利用する方法

スピルが利用できるExcelではTEXTSPLIT関数で文字列分割ができます。
指定した区切り文字で分割しスピルで値を表示する場合は、TEXTSPLIT 関数の次の書式を利用します。

TEXTSPLIT(文字列またはセル, "区切り文字")

LEFT 関数を利用する方法:指定した区切り文字の左側の文字列を取り出す

指定した区切り文字から左側の文字列を取り出す場合は、LEFT関数と、FIND関数を利用します。
以下の数式で、指定した区切り文字より左の文字列を取得できます。

LEFT(文字列またはセル, FIND(区切り文字, 文字列またはセル)-1)

RIGHT 関数を利用する方法:指定した区切り文字の右側の文字列を取り出す

指定した区切り文字から右側の文字列を取り出す場合は、RIGHT関数と、FIND関数を利用します。
以下の数式で、指定した区切り文字より左の文字列を取得できます。

RIGHT(文字列またはセル, LEN(文字列またはセル)-FIND(区切り文字, 文字列またはセル))

実施例

指定した区切り文字で分割した値を取り出す: TEXTSPLIT関数を利用する

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,","))

指定した区切り文字の左側の文字列を取り出す (LEFT関数の利用)

下図のExcel表を準備します。

セルの文字列は下記のとおりです。","で2つの数値が区切られた文字列です。

32,45

11,5

82,34

6,9

12,8

22,45

C2セルに下記の数式を入力します。

=LEFT(B2,FIND(",",B2)-1)

LEFT関数を用いてB2のセルの先頭(左側)から指定した文字列を抜き出します。何文字抜き出すかが FIND(",",B2) の部分になります。FIND関数を用いて","までの文字数を取得します。FINDの戻り値をそのまま利用すると、","を含んだ文字列が取得されてしまうため、1を引いて、","の手前までの文字列を抜き出します。

数式の入力を確定します。","の手前までの文字列である"32"が取り出せました。

C2セルの数式をコピーしC列の他のセルにペーストします。他のセルでも同様に、","の手前までの文字列がC列のセルに表示されます。

","の手前までの文字列を取得できました。

指定した区切り文字の右側の文字列を取り出す (RIGHT関数の利用)