文字列を結合し、つなぎ合わせる/&、CONCATENATE

文字列をつなぎ合わせるには、以下の数式、関数を利用します。

=文字列1 & 文字列2 & [文字列3]・・・

[文字列1] = 結合する文字列の1つ目を指定します。
[文字列2] = 結合する文字列の2つ目を指定します。

数式の説明 指定した文字列同士を結合する数式
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

=CONCATENATE(文字列1, 文字列2, [文字列3]・・・)

[文字列1] = 結合する文字列の1つ目を指定します。
[文字列2] = 結合する文字列の2つ目を指定します。

関数の説明 指定した文字列同士を結合する関数。
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

文字列を結合したい場合には、エクセルでは以下の2つの方法が利用できます。

・「&」を使用した数式
・CONCATENATE関数を使用した数式

使い方例)「&」を使用した数式、CONCATENATE関数

下の表は、ある卓球クラブが夏季合宿を実施する際に作成した参加者名簿です。現在C列・D列にて分けて入力されている住所を、E列で結合したいと考えています。
「&」を使用した方法、「CONCATENATE関数」を使用した方法でそれぞれ文字列の結合をしてみましょう。

結合

① 「&」を使用して住所を結合する。

(1)「=」を入力します。
(2)続けて、1つ目の文字列であるセル「C4」と2つ目の文字列セル「D4」を「&」で繋げて入力します。
=C4&D4

② CONCATENATE関数を使用して住所を結合する。

(1)[文字列1] = 1つ目の文字列のセル「C5」を指定します。
(2)[文字列2] = 2つ目の文字列のセル「D5」を指定します。
=CONCATENATE(C5&D5)

 

POINT!
「&」を使用した数式での結果と、「CONCATENATE」関数を使用した結果は同じなので、どちらを使用しても問題ないでしょう。違いとしては、CONCATENATE関数は、結合できる文字列の数が最大で30個までと決まっています。

 

sponsored link

文字列抽出・分割する/LEFT、MID、RIGHT

とある文字列から、特定の位置にある文字列を抜き出したい場合は、以下の関数を利用します。
データに余計な文字が入っていて、それを除去するときなどに利用されます。

=LEFT(文字列, 文字数)

[文字列] = 取り出す文字が含まれている、元になる文字列を指定します。
[文字数] = 取り出す文字数を指定します。

関数の説明 「左端」から指定した文字数の文字を抽出する関数。
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=MID(文字列, 開始位置, 文字数)

[文字列] = 取り出す文字が含まれている、元になる文字列を指定します。
[開始位置] = 文字列から取り出す位置の先頭を数値で指定します。

[文字数] = 取り出す文字数を指定します。

関数の説明 「開始位置」から指定した文字数の文字を抽出する関数。
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=RIGHT(文字列, 開始位置, 文字数)

[文字列] = 取り出す文字が含まれている、元になる文字列を指定します。
[文字数] = 取り出す文字数を指定します。

関数の説明 「右端」から指定した文字数の文字を抽出する関数。
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

以下のような状況に応じて、3つの関数を使い分けるようにしましょう。

・左端の文字を取り出す →LEFT関数
・中間にある文字を取り出す →MID関数
・右端の文字を取り出す →RIGHT関数

 

使い方例)文字列抽出/LEFT、MID、RIGHT関数

下の表では、C列に住所がまとめて入力されています。今回、D~F列にそれぞれ「都道府県」・「都道府県以下」「番地」と分けて入力し直したいと考えています。幸いなことに、「都道府県」と「番地」は、いずれの住所も文字数が3文字ずつとなっています。
そこで、文字列の中の特定位置の単語を抜き出す、各抽出用の関数を使用して「都道府県」・「都道府県以下」「番地」を分けてみましょう。

抽出

① LEFT関数を使用して、住所の左端にある「都道府県」を取り出す。

都道府県は左から数えて3文字分記載されており、左から3文字抽出するには以下のように指定します。
(1)[文字列] = 取り出す対象となる元の文字列=セル「C4」を指定します。
(2)[文字数] = 取り出す文字数は、左端から3文字分なので、「3」を指定します。
=LEFT(C4,3)

② MID関数を使用して、住所の中間にある「都道府県」と「番地」を除いた住所を取り出す。

都道府県以下で、番地以外の住所が記載されているのは、左から4文字目以降の6文字です。これを抽出する方法は以下の通りです。
(1)[文字列] = 取り出す対象となる元の文字列=セル「C4」を指定します。
(2)[開始位置] = 左端から数えて4文字目から取り出したいので、「4」を指定します。
(3)[文字数] = 開始位置から数えて6文字分が取り出したい文字数なので、「6」を指定します。
=MID(C4,4,6)

③ RIGHT関数を使用して、住所の右端にある「番地」を取り出す。

最後に、番地は、右から3文字分が番地となります。抽出方法は以下の通りです。
(1)[文字列] = 取り出す対象となる元の文字列=セル「C4」を指定します。
(2)[文字数] = 取り出す文字数は、右端から4文字分なので、「4」を指定します。
=RIGHT(C4,4)

 

文字列を置換して置き換える/SUBSTITUTE、REPLACE

文字列を置換し、文字列の中の一部の単語を別の文字に置き換えるには、以下の関数を利用します。置換したい文字列を指定して別の文字に置き換える場合はSUBSTITUTE関数、置換したい文字の文字列中の位置を指定して置き換えたい場合はREPLACE関数を使用します。
=SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)

[文字列] = 置換したい文字が含まれている、元になる文字列を指定します。
[検索文字列] = 置換したい元の文字列を指定します。
[置換文字列] = 置換後の文字列を指定します。
[置換対象] = 省略可。[検索文字列]が複数ある場合に、左から何番目の文字列を置換するかを数値で指定します。省略すると全て置換されます。

関数の説明 [検索文字列]を指定した[置換文字列]に置き換える関数。
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=REPLACE(文字列, 開始位置, 文字数, 置換文字列)

[文字列] = 置換したい文字が含まれている、元になる文字列を指定します。
[開始位置] = 置換したい文字の先頭の位置を数値で指定します。
[文字数] = 置換したい文字列分の文字数を指定します。
[置換文字列] = 置換後の文字列を指定します。

関数の説明 指定した[開始位置]から[文字数]分を[置換文字列]に置き換える関数。
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例1)SUBSTITUTE関数/置換したい文字列を別の文字列に

下の表では、住所が合併前のものとなっている為、新しい住所「さいたま市」に変更しなければいけません。
ここでは、SUBSTITUTE関数を使用して、合併による変更後の住所に文字を置き換えてみましょう。

置換(SUBSTITUTE)

① SUBSTITUTE関数を使用して、住所の文字列から「大宮市」を「さいたま市」に置換する。

(1)[文字列] = 置換する対象となる文字を含む、元の文字列=セル「C4」を指定します。
(2)[検索文字列] = 実際に置換する元の文字「大宮市」を「”」(ダブルクォーテーション)で囲んで指定します。 →”大宮市”
(3)[置換文字列] = 置換後の文字列「さいたま市」を「”」(ダブルクォーテーション)で囲んで指定します。 →”さいたま市”
(4)[置換対象] = 今回は複数の同じ文字がない為、省略します。
=SUBSTITUTE(C4,”大宮市”,”さいたま市”)

 

POINT!
SUBSTITUTE関数の[置換対象]について、対象となる文字が複数ある次のような場合は数値を指定することで、何番目の文字かを指定して置換することが出来ます。

置換対象を利用した例)

セルA1に、「かわいい白猫エリと白猫ミャーの冒険」という文字列が入力されている場合に、「白猫ミャー」を「黒猫ミャー」に変更したい。(※つまり、2番目の「白」を「黒」に変更したい場合)
→ =SUBSTITUTE(A1,”白”,”黒”, 2 )

 

使い方例2)REPLACE関数/置換したい文字列の位置を指定

同じ表を使用して、今度はREPLACE関数を使用した置換方法を見てみましょう。

置換(REPLACE)

① REPLACE関数を使用して、住所の文字列から「大宮市」を「さいたま市」に置換する。

(1)[文字列] = 置換する対象となる文字を含む、元の文字列=セル「C4」を指定します。
(2)[開始位置] = 左端から数えて「大宮市」の先頭は4文字目となる為、「4」を指定します。
(3)[文字数] = [開始位置]から数えて3文字分(大宮市)を置換したい為、「3」を指定します。
(4)[置換文字列] = 置換後の文字列「さいたま市」を「”」(ダブルクォーテーション)で囲んで指定します。 →”さいたま市”
=REPLACE(C4,4,3,”さいたま市”)

 

 

POINT!

SUBSTITUTE関数、REPLACE関数ともに方法は違いますが、同じように置換結果を得ることが出来ます。
ただし、

・SUBSTITUTE関数は、[検索文字列]として、元になる文字を直接指定することが出来る。
・REPLACE関数は、置換する元の文字を指定せずに置換出来る。

といった違いがあり、元となる文字列が色々と違っている場合にはREPLACE関数が有効となるでしょう。