これだけで文字列操作は完璧!Excelの文字列操作関数12個を厳選

こんにちは、エクセル使いこなし隊 隊長の吉井良平です。

 

エクセルでは、数字だけじゃなくて文字列にも関数が使えます。

「絶対覚えるべき」という感じではないのですが、文字を操作することができれば、エクセルの用途が広がっていきますので、エクセルで名簿や住所録を管理されている方にはおススメです。

 

今回紹介した関数以外にもいくつか関数はありますが、ここに紹介した12種類の関数さえ知っておけば、ほとんどの場合に対応できるはずです。

全部覚える必要はまったくありません。「こんなことも出来るんだ」という感じで読んでいただいて、何かの際に「確か、こういうことが出来るはず・・・」と思い出せば大丈夫です。

 

文字列とは?

 

「文字列」とは、文字、または文字が集まったもののことです。(そのままですけど)

エクセルだと、セル(マス目)に入力できるのは、文字・数値・数式の3種類しかないので、数値と数式以外のもの、ということもできます。

 

一点、注意しておく必要があるのは、数値のように見えて実は文字列であることが、たまにあります。

 

エクセルの数字と文字列

数字を文字列として入力した場合

 

上記の例では、真ん中の漢数字で書いた部分は文字だとすぐ分かりますが、右側の方は数字ですが文字列です。

数字が文字列になっている場合は、セルの中で左側に揃うので、そんな時には「これは文字列として入力されているんだな」と思ってくださいね。

 

また、数値は合計などの計算ができますが、文字列は計算ができません。計算結果がおかしい場合も、文字列だな、と分かります。

ID番号、会員番号などは、数字よりも文字としておいた方が良いこともあるので、こういうことがあります。

 

数字を文字列に、文字列を数値に

 

ちなみに、数字を文字列として入力する方法は、「’1」のように、数字の前にアポストロフィーを入れるか、セルの書式設定で文字列にします。

 

セルの書式設定で文字列にする

セルの書式設定で文字列にする

 

文字列として入力されている数字を、計算できるように数値に戻すには、VALUE関数を使います。

VALUE関数

文字列を数値に

 

 

それでは、文字列の操作関数を、機能別に分類して紹介していきます!

 

文字列を結合する

 

CONCATENAETE関数

 

バラバラに入力された文字列を、一つにまとめる関数です。

=CONCATENATE(文字列1,文字列2・・・)

CONCATENATE関数使用例

使用例

 

でも・・・あまり使いません。

姓と名を結合する場合など、2つぐらいの文字列をまとめる場合は、関数を使わずに ”&” (アンパサダント)の記号を使った方が簡単です。

 

&で文字列をつないだ場合

&で文字列をつないだ場合

 

&でつなぐ方法は便利なので、覚えておいてくださいね。

もし、姓と名の間にスペースを入れたい場合は、「=A2&” “&B2」のように、間に””(ダブルコーテーション)の間にスペースを入れたものを、&で挟みます。

 

文字列の長さを計る

 

LEN関数

 

文字の長さを計る関数です。

=LEN(文字列)

LEN関数使用例

LEN関数使用例

 

例えば、ウェブサイトのタイトルは、Googleの検索結果に32文字しか表示されないので、できればその文字数に抑えたいですよね。(*40文字ぐらいまで大丈夫という説もあります)

また、「100字以内で自己紹介を」などという場合も、よくあります。

そんな文字数制限がある場合には、このLEN関数は結構便利です。

LENの後に、()でセルを指定するだけなので、使い方も簡単です。

 

文字列を切り取る

 

文字列を切り取る関数には、いくつか種類があります。

 

LEFT関数

 

その名の通り、文字列の左側を切り取る関数です。

=LEFT(文字列,文字数)

 

具体例として、ネットから東京大学合格者数ランキングをひろってきて、住所欄の先頭 3文字分を切り取りました。(余談ですが、僕が大学受験をした頃から考えると、このランキングもだいぶ変わってますね!)

 

LEFT関数使用例

LEFT関数使用例

 

D列に、都道府県名が切り取られていますね。

こんな感じで、住所から都道府県を切り取ったり、CONCATENATE関数とは逆に姓だけを切り取ったりする場合に使います。

(神奈川に”県”が表示されていないことに関しては、後で詳しく書いています)

 

RIGHT関数

 

LEFTがあるなら、RIGHT関数もあって、右側だけを切り取ることもできます。

=RIGHT(文字列,文字数)

 

RIGHT関数使用例

先ほどの住所から、右側3文字を切り取った例

 

今度は、住所欄の右側から3文字分を切り取ってみました。

この場合は、意味を為さないデータになっていますが、例えば品番の先頭4桁が型番で、右側の4桁が連番になっている、といった場合に、連番部分を切り取ることができます。

後は、姓名の名前部分のみを切り取る場合にも使えます。

 

MID関数

 

LEFTがあって、RIGHTがあるのであれば、中央部分を切り取るMID関数というのもあります。

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

 

MID関数使用例

4文字目から3文字分を切り取った

これも意味をなさないデータになっていますが、電話番号の中から市内局番だけを取り出すとか、決まったルールで作られている文字列に関しては、MID関数で必要な部分を切り取ることができます。

 

文字列から特定の文字を探す

 

先ほどの切り取り系の関数では、意味のない値が表示されてしまいましたよね。

そんな時は、FIND関数というのを、使います。

=FIND(検索文字列,対象文字列)

FIND関数使用例

県という文字を探す

 

都道府県名をちゃんと取り出すために、”県”という文字が何番目にあるかを探してみました。

(県という文字が無い場合は、エラーになっています)

 

”県”という文字が何番目に出ているか(D列)で、そこまでの文字を切り取る式にすると、ちゃんと都道府県を切り取ることができます。

(エラー値があるので、式がややこしくなっていますが)

 

4文字と3文字で条件を分けた

4文字と3文字で条件を分けた

 

今回は、”都”が入ってきているので、ちょっと分かりにくいのですが、FIND関数とLEFT関数を組み合わせることで、住所から都道府県を切り取ることができます。

魔九郎
これはちょっと難しい・・・  

 

電子メールアドレスを切り取ろう

 

ややこしいと思うので、もう一つ例題をやってみますね。こちらの方が簡単・・・かもしれません。

電子メールのアドレスから、アカウント名とドメイン名を切り分けてみます。

 

文字列操作関数の組み合わせ

電子メールを分ける場合

 

① まず、電子メールアドレスから、@の位置をFIND関数で調べます。@より左側がアカウント名で、右側がドメイン名ですよね。

 

② 次に、LEFT関数で、電子メールから@より左側部分を切り取ります。この場合の式は、=LEFT(B4,C4-1)です。C列で求めた@の位置から、1を引いた文字数を、B列から切り取っています。

 

③ LEN関数でB列の文字数を調べておいて、そこから@の位置を引いた数が、ドメイン名の文字数になりますよね。その文字数分を、RIGHT関数で切り取ります。

 

慣れないうちはややこしいですが、「文字列操作関数をいろいろ組み合わせればできる」ということが分かっていれば大丈夫です。

必要は発明の母で、必要な時に思い出してやってみて下さい!

 

文字列を置き換える

 

文字列を置き換える関数には、SUBSTITUTE関数と、REPLACE関数があります。

 

SUBSTITUTE関数は、特定の文字を特定の文字に置き換える関数です。例えば、○○町が△△市に合併した場合に、「○○郡」を「△△市」に置き換える場合に使います。

ただこの場合は、関数を使うよりエクセルの置換機能を使った方が早いので、REPLACE関数のみ紹介します。

 

エクセルの置換ボタン

エクセルの置換機能の位置

 

REPLACE関数

 

文字列の指定の位置から、指定文字数分を置き換える関数です。

個人情報を隠したい場合などに使います。

=REPLACE(対象文字列,開始位置,文字数,置き換え文字列)

REPLACE関数使用例

REPLACE関数使用例

 

この例では、個人名を隠すために、B列の2文字目から3文字分を、○○○の伏字に変更しています。

最後の行は、名前が5文字なので最後の文字が出てきてしまっています。これを表示させないようにするには、先ほどのように、LEN関数を組み合わせて文字数を指定すれば大丈夫です。

 

 

文字列を変換する

 

住所録などで、入力する人によって全角と半角が入り乱れることもありますよね。

そんな時には、文字列を変換する関数を使います。

 

全角を半角に変換する

=ASC(文字列)

ASC関数

全角を半角にした

 

半角を全角に変換する

=JIS(文字列)

JIS関数

半角を全角にした

 

大文字と小文字を変換する

 

外資系の会社や、海外部門の人は英語の大文字と小文字を変換することもあるでしょう。

そんなときは、UPPER関数(すべて大文字にする)、LOWER関数(すべて小文字にする)、PROPER関数(単語の先頭だけを大文字にする)を使います。

 

0612

 

その他の文字列操作関数

 

PHONETIC関数

文字のフリガナを表示してくれる関数です。一見、便利なようですが、最近のようにキラキラネームが増えてくるとフリガナがあてにならない → 間違いの元になる、ということで、私はあまりお勧めしません。

 

EXACT関数

二つの文字列を比較して、同じかどうか調べる関数です。

EXACT関数使用例

EXACT関数使用例

 

ちょっと使用例が思い浮かばなかったので、適当です^^

 

まとめ

 

今回は少しマニアックな内容だったかもしれませんが、住所録などの情報が、県別や都市別になっていない方は、ぜひ試してみて下さいね。

私がこの辺のテクニックを覚えたのは、2003年~2005年の平成の大合併の頃でした。また、いつか大がかりな変更があったとしても、文字列が変換できるということを知っておけば、安心です。

すぐに使う必要が無い方は、「こんなこともできる」ということで、頭の片隅にでも置いておいてくださいね。

 

日本全国エクセル実態調査

文字列操作関数を、ふだん使っていますか?

結果を表示する

読み込み中 ... 読み込み中 ...

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

ABOUTこの記事をかいた人

昭和48年広島県生まれ。現在も広島に住んでいます。 趣味は音楽鑑賞(邦楽、洋楽問わず、クラシック、童謡も)、マンガです。エクセルを効率的に使う方法を紹介して、あなたのお役に立ちたいと思っています。