エクセル講座5:原稿整理にすぐに役立つ「文字列関数」

 

文字列関数?? 文字をエクセルで計算するんですかね?
なんだかイメージがわかないです。。。

 
 

実例を見ながら説明していくぞ〜!!
文字を数えたり、文字を見つけたり、置き換えたり!
組み合わせ次第でいろんな処理ができるんじゃ

 

1)文字列関数でできること!

まず、一つ関数の実例を使ってみましょう。
エクセルを立ち上げて、新規作成で新しいシートを作ってください。

下の赤い文字の部分をコピーします。そして、エクセルのA1にカーソルを持っていき、コピペしてください。

○全国に150教室! ○20回までのサポート無料!
☆
=SUBSTITUTE(A1,"○",A2)
=JIS(A3)

文字が入りきらない場合は、A列を左右に広げてみてください。
図のようになれば成功です。
文字が赤くなる、文字の大きさが不揃い、といった場合には、5-5:形式を選択してペーストを読んで、やり直してみてください。

A3に入れた数式に含まれている「SUBSTITUTE」は文字を置き換える関数です。
A1の原稿の「○」を、A2に置き換える指示を書いてあります。
A2には「☆」が入っているので、「○」が「☆」に置き換わります。
A2の「☆」を「★」に変えると、A3、A4の「☆」も「★」に変わります。

A4の数式のJISは、半角を全角にする関数です。
下記の太文字にした部分が、A3から変更されています。
全国に150教室! 20回までのサポート無料

いろんなところから集めた原稿を整理するときに、よく下記のようなことが課題となります。

・テキストの見映えの調整や、指示を渡す際のマークの整理が必要
・原稿に混在している半角や全角の文字を統一することが必要

こういった場合に、エクセルの関数が役立ちます。

 

文字の置き換えも出来るんですね!
でも、こういう作業は普段、ワードでやってますよ

 
 

ワードでも出来るのは確かじゃ。
では、エクセルのよい点を説明しておこう

 

2)ワードでの処理との違い

このくらい、ワードでも簡単にできるんじゃない? と思われる方も多いと思います。
たしかにそうなんです。ワードの検索置換を使ったり、打ち直しで出来ることではあるのです。
ただ、

  • こういう原稿が膨大にある場合
  • 同じようなパターンを何度も処理する場合
  • 打ち直しのミスや検索置換のミスが許されない場合

そういった場合に、エクセル関数で処理すると、

  • 膨大な処理がコピペだけで出来る
  • 何度も同じ処理をできる
  • 原稿の変更過程を確認できるので、後戻りができる。そのためミスを減らすことができる

というメリットがあります。

3)文字列関数の基本中の基本「&」と「””」

ここからは、出来るだけ実例を使って説明していきます。
とくに断り書きがない場合は、赤い文字をA1からコピペしてください。
コピペのときにはエクセル講座3の「5-6:このサイトでのコピペの標準の形【重要】」のやり方でやってみてください。

3-1:文字と文字をつなげる「&」

2018年
紅白歌合戦
=A1&A2

A3は、A1とA2のテキストをつなげた、
2018年紅白歌合戦
になったはずです。
この例のように、「&」を使うと、テキストとテキストをつなげて一つにできます。

3-2:テキストを式の中にいれる時の「””」

3-1の例を少し変えて、A1の「2018年」と紅白歌合戦をつなぐ式を作ってみました。
ためしにコピペしてみてください

2018年
=A1&紅白歌合戦

A2に「#NAME?」と出ましたか? 
この数式を見て、「2018年紅白歌合戦」にしたいこっちの気持ちを汲んでくれればよいのですが、エクセル君はその辺不器用なので、しょうがありません。
「#NAME?」というのは、関数が間違ってますよ、エラーですよ、という返答なんですが、なんだか、ほんとに嫌な感じしますよね。
NAMEてんの(- -#?)とこっちが聞きたくなります。

こういう時どうするかというと、紅白歌合戦を「””」で囲んであげます(半角のダブルクォーテーション。Shiftキーを押しながら「2」です)。

2018年
=A1&"紅白歌合戦"

こうすると、ちゃんと「2018年紅白歌合戦」になります。
このように、関数の中に、普通のテキストを入れるときは「””」で囲みます。

3-3:何もないセルの「””」

3-2で説明したように、エクセル君は、関数式の中では下記のように認識します。

"紅白歌合戦" → 紅白歌合戦

では、下記の場合はどうなるか?

""

これは、「””」の間になにもないので、何もなし、ということになります。

この「””」は、文字を消去する場合などに使えます。

新宿警察署
=SUBSTITUTE(A1,"新宿","")

この数式の意味は、
A1の「新宿警察署」の中の「新宿」を「(何もなし)」に置き換えるというもの。
つまり、「新宿」を削除することになります。
A2には、「新宿」が削除されて「警察署」だけが入ります。

3-4:スペースを入れてつなげる

テキストとテキストをつなぐ時に、間にスペースを入れたい。
そんな時は「” “」を使います!(””の間にスペースが入ってます)

Tokyo
2020
=A1&" "&A2

こうすると、Tokyoと2020の間にスペースが入って「Tokyo 2020」となります。
「””」の間に入れるスペースは半角にも全角にもすることができます。半角2個、全角2個とか、その組み合わせも大丈夫です。

今回の例では、欧文の「Tokyo」と半角数字の「2020」の間のスペースは、半角1つが適正です。そこで、「””」の間に半角スペース1つを入れた式にしてあります。

 

たしかに、こういう時に一つずつスペース入れながら打ち直すの面倒なんですよね。
もしかしたらエクセル関数使えるのかも。。。

 
 

いやいや、使えるんじゃが、まだ疑われてるのう(^^)
この後が、いよいよ文字列関数の中でもすごく大事なものなんじゃ

 

4)文字を置き換える「SUBSTITUTE」関数

エクセルで文字を扱う上で、非常に重要なのがSUBSTITUTEです。
英語のsubstituteという単語は、どんな意味なのでしょう?
weblioで調べてみたら「取り替える、代わりに用いる」と出ていました。用例は「バターの代わりにマーガリンを用いる」。
まさに、この関数で出来るのはそういうこと。
文字を置き換えるときの関数です。

4-1:SUBSTITUTE関数の使い方

料理本のレシピを編集する場合で考えてみましょう。元々のレシピではバターを使うことにしていたけれど、諸事情により全部マーガリンに置き換えることになった、という場合です。
下記をエクセルのA1に貼ってみてください。

バター大さじ1
=SUBSTITUTE(A1,"バター","マーガリン")

これでA1の「バター大さじ1」がA2では「マーガリン大さじ1」になります。

SUBSTITUTE関数では、次のように( )の中を並べます

=SUBSTITUTE(対象となるセル,置換前の言葉,置換後の言葉)

サンプルの関数でバターとマーガリンが「””」で囲んであるのは数式の中にテキストを入れる時にエクセル君に、
「ここからテキスト入れさせてもらうよ」
と伝えるためです(先ほど3-2で説明しましたが念のため)。

 

テキストを直接打ち直さなくても、関数で直せるんですね!
でも、原稿のバターをマーガリンに変更する事態ってよっぽどですよね?
まずあり得ないと思いますよ!?

 
 

まあまあ、そうディスらず(^^;
バターとマーガリン以外でも使える、こんな方法はどうじゃろう?

 

4-2:SUBSTITUTE関数で、言葉を置き換えるリストを作る

上の例だと、バターをマーガリンに置き換える場合にしか使えませんが、これを他の言葉でも扱えるようにしてみます。下記をコピペしてみてください。

卵2個、バター大さじ1、砂糖大さじ2
バター
マーガリン
=SUBSTITUTE(A1,A2,A3)

SUBSTITUTE関数は、()の中を次のように並べるのでした。
=SUBSTITUTE(対象となるセル,置換前の言葉,置換後の言葉)

今回の式、=SUBSTITUTE(A1,A2,A3) の場合、
対象となるのはA1です。
そのA1の中のA2を、A3に置換する。という意味になります。

バターがマーガリンに変わりましたね?
そこまでうまくいったら、今度はエクセルのA2とA3を打ち直してみます。

A2 バター → 砂糖
A3 マーガリン → 塩

こうすると、
「卵2個、バター大さじ1、塩大さじ2」
に変わったはずです。

4-3:用語統一とSUBSTITUTE関数

原稿をまとめる上で、言葉の統一が必要になる場合は多いです。
「iphone」「iPhone」「アイフォン」と原稿の中で表記が混在していたら統一したいところです。
難しい漢字をひらがなにする場合もあるでしょう。
そんな時にも、SUBSTITUTE関数を使うことができます。

ちょっと長いですが、下記をコピペしてみてください。

iphoneもusbで繋ぐことになり、アイフォン用のケーブルは不要になる。
iphone
iPhone
=SUBSTITUTE(A1,A2,A3)
アイフォン
iPhone
=SUBSTITUTE(A4,A5,A6)
usb
USB
=SUBSTITUTE(A7,A8,A9)
繋ぐ
つなぐ
=SUBSTITUTE(A10,A11,A12)

一番上の原稿がだんだん加工されていっているのがわかると思います。

iphone「iPhone」に置き換え
アイフォンを「iPhone」に置き換え
usbを「USB」に置き換え
繋ぐを「つなぐ」に置き換え

一番最初の、
「iphoneもusbで繋ぐことになり、アイフォン用のケーブルは不要になる。」が、
最後は、
「iPhoneもUSBでつなぐことになり、iPhone用のケーブルは不要になる。」
になりました。

 

確かにこういう統一って面倒なんですよね。
こういうリストを作って、うまくやると処理ができるってことですね?

 
 

そういうことじゃ。
この例の形を応用することで、用語の統一など、いろんな処理が可能になるんじゃ。

 

5)テキストの一部分だけを切り取る「LEFT」「MID」「RIGHT」関数

文字数を指定して、必要な部分だけ切り取るのがこの3つの関数です。

LEFT:左から何文字切り取るかを指示します。
MID:切り取るのは途中からです。左から何文字目から始めるかを指定して、そこから何文字分か指示します。
RIGHT:右から何文字切り取るかを指示します。

サンプルで試してみてください。

長野県松本市神林5300
=LEFT(A1,3)
=MID(A1,4,3)
=RIGHT(A1,6)

この例では、A1のセルから、それぞれ何文字かずつどこを切り取るか指示を与えています。
A2の式「=LEFT(A1,3)」は、「A1の左から3文字分残す」
A3の式「=MID(A1,4,3)」は、「A1の4文字目から3文字分残す」
A4の式「=RIGHT(A1,6)」は、「A1の右から6文字分残す」
という意味です。

これで、
A2は「長野県」
A3は「松本市」
A4は「神林5300」
が残ります。住所を県、市、地番に分割できました。

 

これって、県名や市の名前が3文字ならいいけど、
他の文字数だと使えなくないですか???

 
 

その通り! 「長野県」「秋田県」「徳島県」「新宿区」は3文字だからよいが、
4文字の「鹿児島県」とか、2文字の「港区」とか、不都合な場合がある

 
 

じゃあどうするんですか?

 
 

「県」「市」「区」のような言葉が、何番目に来るかを調べて指示すればよい。
そのために「SEARCH」関数を組み合わせるといいんじゃ

 
 

SEARCHですか?
探すのなら得意ですダイジョブっす!
任せてください。何探しますか? どこ探しますか?

 

6)何文字目にある?と探すための「SEARCH」関数

「SEARCH」関数は、エクセル君に、
「A1の文字の中で、”県”が何文字目に入ってるか見つけておいで~」
といった指示に伝えるものです。
これを使うと、エクセル君が下記のように”県”を見つけてきます。
A1が「長野県xx市」の場合は3
A1が「鹿児島県xx市」の場合は4

この文字数をLEFT関数で切り取れば、長野県の場合は3文字分、鹿児島県の場合は4文字分を切り取ることができるのです。

では実例です。
またA1に下記をコピペしてみてください。

鹿児島県鹿児島市
=SEARCH("県",A1)
=LEFT(A1,A2)

A3に「鹿児島県」が入るはずです。
やってみてください。

このやり方だと、県名が4文字でも3文字でも対応できます。
試しに「鹿児島県」の部分を「長野県」にすると、A2の数字が3に変わって、A3には「長野県」だけ残ります。

 

「SEARCH」と「LEFT」、2つの関数を組み合わせると、
こんなことが出来るんですね!

 
 

そう、1つの関数だと「これが一体、何に使えるの?」というものでも、
組み合わせることで、いろんなことが出来るのじゃ

 

7)前後の余計なスペースを削除する「TRIM」関数

いろんなところから集まったテキストは、前後に余計なスペースが入ってしまうことがあります。
とくに誰かに入力してもらった場合に入るケースが多いです。
この余計な前後のスペースを削除するのが「TRIM」関数です。
全角スペース、半角スペースの両方とも削除してくれます

それでは実例を作ってみましょう。
今回も下記の赤い部分をコピペしてみてください。

 福岡県北九州市小倉北区   
=TRIM(A1)

A1のテキストは、Web上でコピーしたもので、前後に不要なスペースが入ってしまいました。
A2ではその、前後のスペース削除の指示を出しているので、なくなっています。

 

SEARCH関数や、LEFT関数などで、文字を取り出す前に、
TRIM関数で下処理をするとよいのじゃ

 
 

下処理ですか? 料理みたいな感じ?

 
 

そう。テキストを取り出してから、前後にスペースがあったりすると面倒。
最初に一手間をかけておくと、後々の作業が楽になることがあるのじゃ

 

8)文字数を数える「LEN」関数

原稿の分量を揃えたいときに、文字数を数えたい場合があります。
それをやってくれるのが「LEN」関数です。

下記をA1にコピペしてみてください。

館内のWiFi整備について
=LEN(A1)

A2に13と表示されれば合っています。
試しに、A1にご自分のお名前を打ってみてください。A2の数字が、お名前の文字数に変わるはずです。

もちろん、数千字あるような原稿をコピペしても大丈夫です。
改行の多い文章であれば、A1のセルの中にカーソルを置いて、コピペすればテキストが全部A1の中にペーストできるので、計算してくれます。
このペーストについては、エクセル講座3の「5-4:カーソルをセルの中に置いてペースト」を参照してください。

 

これ使って、写真のキャプションとかを整理したら便利かもしれないですね。。
セルじい、なかなかやるじゃん!

 
 

長い文章の文字数カウントも、ワードの「文字カウント」より速いからおすすめじゃ

 

9)文字を全角にする「JIS」関数、半角にする「ASC」関数

数字や欧文などを全角や半角に統一する関数が「JIS」と「ASC」です。
これはこれで便利ですごいのですが、エクセル君は気が効かないので、使うのは少し難しいです。

少し難しいというのは、、、、

・全角にすると、iPhoneやAppleみたいな言葉も全角になってしまう。
・半角にすると、カタカナも半角になってしまう。

という点です。

下記の実例をコピペしてみてください。

ギズモード・ジャパンがAppleを取材
=JIS(A1)
=ASC(A1)

A2ではAppleが全角に、A3ではギズモード・ジャパンが半角になっています。
日本語の中に欧文が入るケースが増えているので、この扱いは難しいところです。

 

日本語原稿の中に、inc.とかRakutenとか、Android、USB等々、
欧文の半角文字が入るケースが増えている。
JIS関数、ASC関数で簡単に処理できるとよいけれど、
実際にはイレギュラーも多くてなかなか難しいんじゃ

 
 

珍しく弱気ですね。。
要は使いようってことですね!

 

10)まとめ

ほかにも、文字列関数はたくさんあるのですが、非常に代表的なものを紹介してみました。
「そうか、こんなこと出来るんだ!」と思っていただけたら幸いです。

ここまでが、基本編。

次からは、これら、エクセルの基本を活かした処理を書いていこうと思います。

 

超絶なエクセル技を紹介していくぞーー!

 
 

なんだか、自分でハードル上げてますね!
楽しみにしてますよ(笑)

 
hosokawakobo
細川生朗 Hosokawa Seiro
1967年生まれ。1991年に情報センター出版局に入社。『水原勇気0勝3敗11S』『いちど尾行をしてみたかった』『笑う出産』などのヒット作を編集。1994年に『きょうからの無職生活マニュアル』、1998年に『旅の指さし会話帳①タイ』を企画・編集。いずれも累計100万部以上のシリーズとなる。2001年に情報センター出版局を退職。その後、フリーの編集者として、実用書を中心にした単行本の企画・編集、自費出版の写真集や記録集の編集、社史の編纂などを手がけつつ、指さし会話帳シリーズの編集も続けている。