Excel。日付から年月日をTEXTSPLIT関数で分割し抽出する方法【date】

Excel。日付から年月日をTEXTSPLIT関数で分割し抽出する方法

＜TEXTSPLIT+TEXT関数＞

日付から、年月日をそれぞれのセルに分割して抽出したい場合、年ならば、YEAR関数。

月ならば、MONTH関数。

日ならば、DAY関数とそれぞれ異なる関数をつかって抽出する必要がありました。

単純な関数ですが、関数が異なっているので、数式をつくるのは、面倒です。

そこで、決まった文字で文字列を分割することができるTEXTSPLIT関数をつかうことで、一つの数式だけで、対応することができます。

B2にTEXTSPLIT関数で数式をつくってみます。

=TEXTSPLIT(A2,"/")

これだと、分割どころか、結果がシリアル値で表示されてしまっています。

原因は、日付はシリアル値。すなわち数値だからです。

TEXTSPLIT関数は、文字を分割する関数なので、数値は分割できません。

そこで、日付をTEXT関数で、文字化させます。

B2の数式を修正します。

=TEXTSPLIT(TEXT(A2,"yyyy/m/d"),"/")

A2の日付をTEXT関数で文字化しました。

TEXT関数は表示形式を設定することができる関数です。

これで、年月日に分割し、抽出することができました。

TEXTSPLIT関数は、スピル機能に対応した関数なので、行方向には、ゴーストが発生するのでオートフィルによるコピーは不要です。

あとは、列方向に、オートフィルで数式をコピーして完成です。

この方法だと、複数の関数をつかって、年月日を抽出する必要はありません。

ただ、抽出された数値は、左揃えになっています。

つまり文字になっています。

そこで、右揃えにするというのでも、いいのですが、文字型を数値型に戻すように数式を再度修正します。

=TEXTSPLIT(TEXT(A2,"yyyy/m/d"),"/")*1

先程の数式に「*1」をつけるだけで、文字型を数値型に変えることができます。

なお、B2は日付表示になってしまうので、表示形式を「標準」に戻す必要があります。

あとは、オートフィルで数式をコピーします。

TEXTSPLIT関数は、セル内の文字を分割することができる関数なので、アイデアで色々使える可能性があります。

Excel。「,」で区切られた文字列を別々のセルにわけ、その文字列数も手早く確認したい【comma】

Excel。「,」で区切られた文字列を別々のセルにわけ、その文字列数も手早く確認したい

＜TEXTSPLIT関数＋COUNTA関数＞

セル内の「,」で区切られた文字列数を求めるには、

LEN関数とSUBSTITUTE関数をつかうことで、求めることができました。

例えば、

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

というような数式ですね。

ところが今回は、B列の「,」で区切られた文字列数を別々のセルにわけて、表示したいわけです。

そして、文字列数も合わせて知りたいといのがリクエストです。

用意した表で確認してみましょう。

B列には、「,」で区切られた文字列が入力されています。

まず、「,」で区切られた文字列を別々のセルに表示させたい。

そこで、TEXTSPLIT関数をつかいます。

D2の数式は、

=TEXTSPLIT(B2,",")

最初の引数は、該当するセル番地なので、B2

次の引数は、区切りのマークを設定します。

「,」で区切られていますので、「”,”」とすれば、ゴーストが発生して、別々のセルに表示してくれます。

あとは、文字列宇数を数えたいので、COUNTA関数をつかえば完成です。

=COUNTA(D2:H2)

ゴーストも対象になっていますので、COUNTA関数というシンプルな関数で対応することが可能です。

なお、別々のセルに分割表示しないで、文字列数だけを求めたいならば、LEN+SUBSTITUTE関数で対応できます。

Excel。氏名分割は、TEXTSPLIT関数だけで手早く分けることができます。【Split】

Excel。氏名分割は、TEXTSPLIT関数だけで手早く分けることができます。

＜TEXTSPLIT関数＞

フルネームの氏名から苗字と名前を分割したい場合、フラッシュフィルをつかう、あるいは、LEFT関数やMID関数など複数の関数をつかって、処理をしていました。

ところが、Microsoft365（Excel365）に登場した「TEXTSPLIT関数」をつかうと、スピル機能も加わって、TEXTSPLIT関数のみで、苗字も名前も分割することができるようになりました。

なお、前提条件として、苗字と名前の間は、「” “（半角スペース）」で区切られているものとします。

B2にTEXTSPLIT関数をつかった、数式を設定します。

=TEXTSPLIT(A2," ")

すると、スピル機能によって、自動的にC2も数式が反映されます。

あとは、オートフィルで数式をコピーすれば、完成です。

このように、新しく追加された、TEXTSPLIT関数の登場で、フラッシュフィルだけでなく、関数でも手早く、苗字と名前を別々にすることができるようになりました。

TEXTSPLIT関数の引数も確認しておきましょう。

TEXTSPLIT関数

読み方： テキストスピリット　　

分類： 文字列操作 

TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pat_with])

1番目の引数は、「text」。

文字列です。

分割対象になる文字列です。

今回は、「A2」。

2番目の引数は、「col_delimiter」。

行区切りです。

複数列に分割するための区切り文字を指定します。

半角スペースで区切りたいので、「” “」と設定します。

3番目以降は、省略できます。

3番目の引数は、「row_delimiter」。

列区切りです。

複数行に分割するための区切り文字を指定します。

4番目の引数は、「ignore_empty」。

空白は無視かどうかを設定します。

TRUEにすると空白文字列を無視します。

無視されるので、詰めて表示されます。

FALSEにすると、空白文字列分のセルが空欄になります。

ミドルネームとかがある場合に指定するといいですね。

5番目の引数は、「match_mode」。

一致モードです。

大文字と小文字を区別して対応することができます。

「0（ゼロ）」だと、大文字と小文字が区分されます。

「1」にすると区分されません。

6番目の引数は、「pat_with」。

代替文字列のことです。

Excelの様々な関数の読み方や引数などを紹介。今回は、TEXT関数～TEXTSPLIT関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、TEXT関数～TEXTSPLIT関数です。

＜Excel関数辞典：VOL.82＞

今回は、TEXT関数～TEXTSPLIT関数までをご紹介しております。

TEXT関数

読み方： テキスト　　

TEXT(値,表示形式)

数値を書式設定した文字列に変換する　

TEXTAFTER関数

読み方： テキストアフター　　

TEXTAFTER(text,delimiter,[instance_num],[match_mod],[match_end],[if_not_found])

文字を区切った後のテキストを返す。
TEXTAFTER(text,delimiter,[instance_num],[ignore_case])から変更　

TEXTBEFORE関数

読み方： テキストビフォー　　

TEXTBEFORE(text,delimiter,[instance_num],[match_mod],[match_end],[if_not_found])

文字を区切る前のテキストを返す。

TEXTBEFORE(text,delimiter,[instance_num],[ignore_case])から変更　

TEXTJOIN関数

読み方： テキストジョイン

TEXTJOIN(区切り文字,空のセルは無視,テキスト1,…)

複数の範囲や文字列からテキストを結合する　

TEXTSPLIT関数

読み方： テキストスピリット　　

TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pat_with])

区切り記号を使用してテキストを行または列に分割する　

Excel。新しく追加されたTEXTSPLIT関数は、簡単にセルごとに分割できます【TEXTSPLIT】

Excel。新しく追加されたTEXTSPLIT関数は、簡単にセルごとに分割できます

＜TEXTSPLIT関数＞

Office Insider のみに追加された関数の中でも結構つかえそうな関数の一つに、今回紹介する「TEXTSPLIT関数」があります。

現在ある、TEXTJOIN関数の逆で、セル内の指定した文字で、行方向でも列方向でも、セルごとに分割してくれる関数です。

次の表は、B2に「内藤,上原,北沢,野堀,由利」という文字が入力されています。

これを「,（カンマ）」で区切られているので、TEXTSPLIT関数をつかえば、簡単にセルごとに分割することができます。

D2には、

=TEXTSPLIT(B2,",")

と設定するだけです。スピル機能が稼働して、数式をコピーしなくても算出してくれます。

B4には、

=TEXTSPLIT(B2,,",")

と設定するだけで、行方向に分割してくれます。

これまた、スピル機能で数式をコピーする必要はありません。

早く正式に追加してくれるといいのですが…

最後に、TEXTSPLIT関数の引数などを確認しておきましょう。

TEXTSPLIT関数

読み方： テキストスピリット

分類： 文字列操作 

TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pat_with])

引数の説明ですが、

最初のtextは、データの文字です。

col_delimiterは、列方向に分割するための区切り文字を設定します。

[row_delimiter]は、行方向に分割するための区切り文字を設定します。

[ignore_empty]は、区切り文字が連続している場合は、空のセルを作成するなら、FALSEを指定します。

[match_mode]は、大文字と小文字の区別をします。

[pat_with]は、結果を埋め込む値です。