Excel。元号年月日が別々の列で入力されているので日付にしたい【date】

Excel。元号年月日が別々の列で入力されているので日付にしたい

＜TEXTJOIN関数＞

データを読み込んでみたところ、次のように年月日が別々の列で入力されていました。

しかも、年は、和暦。

元号のようです。

その元号も昭和ならSというような、アルファベットの頭文字で入力されていました。

これでは、管理しにくいので、E列に日付をつくることにしました。

どのようにしたら、日付に戻すことができるのでしょうか。

単純に&(アンパサンド)で文字結合というわけにもいきません。

単純に文字を結合した結果が表示されるだけです。

そこで、TEXTJOIN関数をつかうことで、対応することができます。

E2をクリックして、TEXTJOIN関数をつかった数式を設定します。

=A2&TEXTJOIN("/",TRUE,B2:D2)

この数式は、まだ途中です。

日付にできたように見えますが、セルの書式設定ダイアログボックスを表示してみると、表示だけが日付っぽく見えていることがわかります。

表示形式の日付にして、カレンダーの種類を和暦にしてから、種類を元号の漢字を選択してみると、サンプルは変わっていないことが確認できます。

そこで、先ほどの数式を次のように修正してみます。

=(A2&TEXTJOIN("/",TRUE,B2:D2))*1

先ほどの数式を「（　）」で囲み、「*1」を追加しています。

これにより、文字型から文字数値型に変わりました。

つまり、シリアル値にすることができたというわけです。

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

そのあと、E2:E5を範囲選択します。

セルの書式設定ダイアログボックスなどをつかって、表示形式を日付にしましょう。

和暦の漢字表示にしてみます。

これで、日付にすることができました。

では、数式を確認しておきましょう。

=(A2&TEXTJOIN("/",TRUE,B2:D2))*1

A列の元号のアルファベットを「&（アンパサンド）」で結合します。

結合するのは、TEXTJOIN関数の結果です。

そのTEXTJOIN関数の確認へ移りましょう。

TEXTJOIN関数は、テキストを区切り文字で接続する関数です。

最初の引数が、区切り文字で、「/」をつかうことにしました。

「-」でもOKです。

2つ目の引数は、空のセルはどうするのかを設定します。

TRUEにすると無視することができます。

B:D列には、データが入力されているので、どちらでもいいのですが、TRUEと設定しました。

3つ目は、テキスト。

接続したい文字です。

接続するのは、B:D列なので、B2:D2　と設定しました。

ここまでの結果に「*1」することで、文字数値型にすることができます。

たぶん、それぞれを　年月日　という文字で区切るよりかは、日付を作るには楽なように思います。

Excel。CCやBCCで一括送信したいので、メールアドレスを「;」で区切りたい【mail】

Excel。CCやBCCで一括送信したいので、メールアドレスを「;」で区切りたい

＜TEXTJOIN関数＞

CCやBCCでメールを一斉送信したい場合には、

メールアドレスのあとに「;（セミコロン）」で区切って、メールアドレスどうしを繋げる必要があります。

次の表を用意しました。

B列には、メールアドレスが入力されています。

メールアドレスごとに「;（セミコロン）」をいれて、連結するので、「&（アンパサンド）」をつかって、文字結合するのは、とても大変な作業です。

メールアドレス数が増えれば増えるほど、途方もない作業になります。

文字結合をするCONCAT関数をつかってということも、できないことはありませんが、メールアドレスのあとに、「;（セミコロン）」を入力しなければなりません。

よって、「＆（アンパサンド）」を使った文字結合と、大差はありません。

このような場合、TEXTJOIN関数をつかうことで、とても簡単にメールアドレスのどうしを「;（セミコロン）」で区切りつつ、連結することも出来ます。

では、B7にTEXTJOIN関数の数式を設定します。

B7の数式は、

=TEXTJOIN(";",TRUE,B2:B5)

これで、「;（セミコロン）」をつかって、メールアドレスを連結することができました。

数式を確認しておきます。

最初の引数は、「区切り文字」。

今回は「;（セミコロン）」で連結したいので「”;”（セミコロン）」と設定します。

2つ目の引数は、「空のセルは無視」。

TRUEは空白ならば無視をします。

FALSEならば、空のセルを含めることができます。

空のセルは含めないので、TRUEと設定します。

3つ目の引数からは、「文字列」です。

メールアドレスが入力されているB2:B5を範囲選択します。

このように、決まった文字で文字列を連結するならば、TEXTJOIN関数をつかってみるといいかもしれません。

Excel。住所録からラベルをつくるので、複数セルを改行して、一つに結合したい【label】

Excel。住所録からラベルをつくるので、複数セルを改行して、一つに結合したい

＜TEXTJOIN＋CHAR関数＞

住所録から、ラベルをつくりたいと考えました。

複数のセルを一つのセルに結合しますが、結合ごとに改行をいれたいわけです。

CONCAT関数だと、改行するたびに、改行を意味する、CHAR(10)を設定しなければなりません。

そこで、TEXTJOIN関数をつかってみることにします。

B7に次の数式を設定します。

=TEXTJOIN(CHAR(10),TRUE,A2:B2,C2&" 様")

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

これで、手早くセルを結合できました。

また結合の間位に改行をいれることもできました。

なお、結合だけの表示になっている場合には、ホームタブにある、「折り返して全体を表示する」をクリックすることで、改行されていることが確認できます。

では、設定した数式を確認します。

=TEXTJOIN(CHAR(10),TRUE,A2:B2,C2&" 様")

TEXTJOIN関数は、セル結合する関数です。

最初の引数は、「区切り文字」です。

結合した時に区切り文字を設定することができます。

今回は、改行したいわけです。改行の文字コードは、CHAR関数をつかって、CHAR(10)とすることで、改行の文字コードをいれることができます。

2つ目の引数は、「空のセルは無視」。

空のセルを無視するならば、TRUEを設定します。

3つ目以降の引数は、結合するセル範囲です。

今回は、氏名の後に「 様」を表示したいので、テキスト1に「A2:B2」。

テキスト2に、「C2&" 様"」と設定しました。

Excel。複数のセル内の文字を指定した文字で、手早く連結したい【connect letters】

Excel。複数のセル内の文字を指定した文字で、手早く連結したい

＜TEXTJOIN関数＞

クレジットカードやIPアドレスなど、例えば、4桁ごとにわかれて入力されているとします。

そのわかれているセルとセルを「-（ハイフン）」で、手早く連結するには、どのようにしたらいいのでしょうか。

次の表で説明します。

 

A1:D5に4桁の数値が入力されている表があります。

A2:D2までの文字列を「-（ハイフン）」で連結したのが、F2です。

文字結合の「&（アンパサンド）」だと、セルとセルの間に「&”-“&」を入力して連結させる必要があります。

そうなると、連結するセルが増えれば増えるほど、面倒になります。

そこで、「TEXTJOIN関数」をつかうことで、手早く連結することができます。

F2に、次の数式を設定します。

=TEXTJOIN("-",TRUE,A2:D2)

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

これだけで、「-（ハイフン）」で連結した文字列をつくることができます。

=TEXTJOIN("-",TRUE,A2:D2)　の数式を確認していきます。

最初の引数は、「区切り文字」。

連結したい文字を設定します。今回は「-（ハイフン）」で連結します。

2つ目の引数は、「空のセルは無視」。

選択したセルに文字が入力されていない場合、無視するならば、TRUE。

無視しないならば、FALSEを設定します。無視しますので、TRUEを設定しました。

3つ目の引数は、テキスト1。

連結したいセルまたは、範囲です。

A2:D2を範囲選択しますので、A2:D2　と設定します。

Excel。年月日が別々のセル、しかも年は短縮元号。日付にするにはどうしたらいい【Era name】

Excel。年月日が別々のセル、しかも年は短縮元号。日付にするにはどうしたらいい

＜TEXT+TEXTJOIN関数＞

データを読み込んだら、なんと日付が、年月日が別々のセルで管理されています。

しかも、年が元号で、昭和を「S」というように表示する短縮した状態でした。

これでは、日付に関する計算や抽出はできませんので、E列に日付をつくることになります。

では、どのようにしたら、アルファベット一文字の元号と年月日をつかって、日付にできるのか、確認していきましょう。

アルファベット一文字の元号の場合、DATE関数をつかって、日付を表示することはできません。

今回のケースでは、表示形式を設定することができる、TEXT関数をつかうことで、日付にすることができます。

E2に次の数式を設定します。

=TEXT(A2&TEXTJOIN("-",TRUE,B2:D2),"ggge年m月d日")

E5まで、オートフィルで数式をコピーすれば、完成です。

年が元号でしたので、日付を和暦（元号）表示にしましたが、グレゴリオ暦（西暦）で表示したいならば、数式は、次のようにすれば問題ありません。

それでは、数式を確認しておきましょう。

TEXT関数は、表示形式の関数です。

元号で表示したいので「"ggge年m月d日"」としています。

あとは、日付はDATE関数をつかうことができませんが、結合はしなければなりません。

年月日は、「/（スラッシュ）」で結合します。

「B2&”/”&C2&”/”&D2」と「&（アンパサンド）」をつかって、何度も繰り返すのも面倒なので、TEXTJOIN関数をつかってみました。

TEXT関数の最初の引数である値には、

A2&TEXTJOIN("-",TRUE,B2:D2)

と設定します。

TEXTJOIN関数は、最初の引数の「区切り文字」で結合することができます。

2つ目の引数は、3つ目の引数の値があった場合、どうするのかを設定することができます。

TRUEだと、空のセルは無視してくれます。

FALSEだと、空のセルを含めてくれます。

空白のセルは存在していないことにしていますので、TRUEとしました。

最後の引数がテキストです。

範囲選択できますので、B2:D2と年月日を範囲選択しています。

これで、日付にすることができました。

アルファベット一文字で短縮された元号と、年月日が別々のセルにある場合には、ちょっとアイディアが必要になります。

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。複数セルの内容ごとに改行して、一つのセルにまとめたい。【put together】

Excel。複数セルの内容ごとに改行して、一つのセルにまとめたい。

＜TEXTJOIN+CHAR関数＞

複数のセルに入力されているデータを、次のように、一つのセルにまとめたいのですが、セルごとの区切りがわかるように、改行してまとめたいわけです。

今回のように、結合する件数が少なければ、CONCAT関数をつかう、あるいは、「&（アンパサンド）」で、文字結合する方法があります。

ただ、問題になってくるのが、単純に結合するのではなくて、セルとセルの間には、改行した状態にしたいわけです。

また、件数が多い場合は、数式を作るのも面倒になってきます。

では、A5には、どのような数式を設定したのかというと、

=TEXTJOIN(CHAR(10),TRUE,B1:D1)

という数式です。

数式を説明していきます。

最初のTEXTJOIN関数は、文字結合を行うことができる関数です。

このTEXTJOIN関数の引数がCONCAT関数や「&（アンパサンド）」よりも効率がいいことがわかります。

最初の引数は、区切り文字です。

改行で区切りたいわけですね。

改行は、CHAR関数はJISコードで割り振られた文字コードを設定することができます。改行は、10番なので、「CHAR(10)」とします。

2つ目の引数は、空白があったらどうするのか。

範囲内に空白があった場合は、無視したいので、「TRUE」を設定します。

最後の引数は、結合したい文字の範囲です。

よって「B1:D1」

これで数式としては完成ですが、このあと、ちょっとした処理をしないといけません。

実行すると、文字が結合されただけのようにしかみえません。

そこで、「折り返して全体を表示する」をクリックすると、改行で区切られていることがわかります。

なお、行幅が広がらない時は、広げてあげる必要があります。

今回紹介したTEXTJOIN関数は、新しい関数です。

新しい関数を色々使ってみると、今までよりも、簡単でわかりやすい数式を作れるかもしれませんので、試してみるといいかもしれませんね。

Excel。「;（セミコロン）」で繋げて手早く一斉メールアドレスをつくりたい【Character combination】

Excel。「;（セミコロン）」で繋げて手早く一斉メールアドレスをつくりたい

＜TEXTJOIN関数＞

テレワークに限らず、多くのスタッフ宛に一斉メールを送りたい場合、メールアドレスごとに「;（セミコロン）」で結合します。

例えば、次のようなスタッフメールアドレス一覧が用意されています。

メールアドレスの間に「;（セミコロン）」をいれた文字列をつくるわけですが、どのようにしたら、手早く作ることができるのでしょうか？

例えば「&（アンパサンド）」で文字列を結合する方法もあります。

=B2&”;”B3…

というような数式になるわけですが、件数が多いので大変ですね。

また、文字結合をする関数である「CONCAT関数」や「CONCATENATE関数」をつかうとしても、「&（アンパサンド）」と同じような数式になってしまいます。

=CONCAT(B2,";",B3,";"…)

それでは、Excel VBAでプログラムをつくるしかないのでしょうか。

そこで、新しく追加された関数「TEXTJOIN関数」をつかうことで、とても簡単につくることができます。

D2に次の数式をつくりました。

=TEXTJOIN(";",TRUE,B2:B10)

数式のままだと、つかえませんので、あとは値のコピーをすることで、一斉メール用のメールアドレスをつくることができました。

では、TEXTJOIN関数を確認しておきましょう。

最初の引数は、「区切り文字」。

結合する文字の間にいれたい文字を設定します。

今回は「;（セミコロン）」で区切りたいので、「”;”」と設定します。

2番目の引数は、「空のセルの無視」。

結合したいセルが空白だった場合、空白にするのか、無視するのかを決めることができます。

今回は、無視することにしますので、「TRUE」と設定します。

この後の引数は、「テキスト1」。結合したいセルを範囲選択してきます。

よってB2:B10と設定します。

文字結合する関数も、色々用意されていますので、用途に合わせて使い分けることで、効率的に処理することができますので、関数を探してみるといいかもしれませんね。

Excel。Office365のExcel2016で追加された、CONCAT関数とTEXTJOIN関数

Excel。Office365のExcel2016で追加された、CONCAT関数とTEXTJOIN関数

＜CONCAT関数&TEXTJOIN関数＞

【CONCAT関数】

次のような表があります。

C列からF列までのセルを結合して、
1つのセルに神奈川県横浜市神奈川区西寺尾としたい場合
どうしたらいいでしょうか？

セルを結合する場合には、”&”で結合する方法がありますよね。

H2に
=C2&D2&E2&F2
という数式を作ると、簡単に文字結合できるので、
神奈川県横浜市神奈川区西寺尾という結果を算出することが出来ます。

ただ、結合するセル数が多いと、”&”で結合するのは結構面倒ですよね。

CONCATENATE関数を使ってみても同じ結果になりますよね。

J2をクリックして、
CONCATENATE関数ダイアログボックスを表示して算出してみましょう。

J2の数式は、
=CONCATENATE(C2,D2,E2,F2)

このCONCATENATE関数でも文字結合できるのですが、
先程の”&”と同じように結合するセル数が多いと、とても大変ですね。

そこで、Office365のExcel2016で新たに追加された関数の一つに、
CONCAT関数（コンカット関数）というのが登場しました。

このCONCAT関数は、CONCATENATE関数の進化版でして、
基本的にCONCATENATE関数と同じく、文字結合することが出来るのですが、
CONCAT関数のメリットは、結合するセル数が増えたときに、
その効果を実感することが出来ます。

L2をクリックして、CONCAT関数ダイアログボックスを表示しましょう。

テキスト1に、C2:F2と入力して、OKボタンをクリックします。

このCONCAT関数は、
なんと、セルを範囲選択できるようになったのです！画期的ですね。

仮に、結合したいセル数が20個でも、
範囲選択するだけで算出することができるのです。

なお、L2の数式は、
=CONCAT(C2:F2)

【TEXTJOIN関数】

文字結合をするのに登場した、
CONCAT関数であっても、
以前からあるCONCATENATE関数や”&”を使ったとしても、
次の場合はどうしたらいいでしょうか？

やりたいのは、C列からF列の文字を結合するのですが、
その間を”-“（ハイフン）で連結していきたい。

つまり、SOU-1-a-内田としたいわけです。

先程と同様に簡単じゃないの？と思われるかもしれませんが、
意外な落とし穴があるのです。

では、H9に”&”を使って数式を作ってみましょう。

H9の数式は、
=C9&"-"&D9&"-"&E9&"-"&F9

セルの間に”-“を入れるだけですが、この数だけでも結構面倒なのに、
10個のセルが対象だったら大変ですよね。

しかも、オートフィルで数式をコピーしてみると、
データがないセルも関係なくいれてしまうので、
H10は、SHO-1--齋藤と、”-“（ハイフン）が連続してしまっています。

当然、”-“（ハイフン）は不要ですから削除するためには、
数式を変更しなければいけません。

では、CONCATENATE関数を使ってみたらどうなるでしょうか？

I9にCONCATENATE関数ダイアログボックスを表示しましょう。

途中に”-“（ハイフン）を設定していく必要がありますので、
セル数が少ない場合はいいですが、多くなると、先程の”&”同様に、
面倒な作業が発生してしまいます。

I9の数式は、
=CONCATENATE(C9,"-",D9,"-",E9,"-",F9)

そして結果を見てみると、

データのないセルのところは、“&”と同じく、
”-“が重なってしまっていますね。

そこで、この問題を解決するために登場したのが、TEXTJOIN関数なのです。

J9にTEXTJOIN関数ダイアログボックスを表示しましょう。

区切り文字には、"-"
今回は、”-“（ハイフン）で区切りたいので、”-“としています。

空のセルは無視には、TRUE
これをTRUEにすると、空のセル。
つまりデータがないところは関与しなくなります。

テキスト1以降は、結合したいセルをクリックしていきます。

そして、OKボタンをクリックして、
オートフィルで数式をコピーしてみましょう。

注目する点は、J10とJ12。データがないところは関与しないので、
”-“（ハイフン）が重なっていませんよね。

あまり使う機会はないのかもしれませんが、
新しく加わった、CONCAT関数とTEXTJOIN関数というのがあるということを
知っておいてもいいかもしれませんね。