らくらくIT活用術
PC活用講座 - Excel
「Excelの便利機能活用術」
「東京本社」から地区名「東京」だけを簡単に取り出すワザとは
ITライター 立山秀利
「東京本社」や「名古屋支社」などの拠点名が多数入力された一覧表があり、それらから地区名だけを取り出したいとする。そのような処理にはREPLACE関数が便利だ。加えて、同関数の応用として、7桁の郵便番号にハイフンを挿入する方法も紹介する。
REPLACE関数で空の文字に置き換える
例えば図1のように、A2～A6セルには拠点名が入力された表があるとする。これらのセルのデータから「本社」などを除き、地区名だけを隣のB列に取り出したいとする。
図1
最後2文字はA2セルが「本社」、A3～A4セルが「支社」、A5～A6セルが「支店」と語句がバラバラである。同じ語句なら、SUBSTITUTE関数で空の文字列に置き換えればよいが、バラバラの語句なのでその方法は使えない。
このような体裁の拠点名から地区名を取り出す方法は何通りか考えられるが、今回はREPLACE関数を用いた方法を採用する。REPLACE関数は文字列を置き換える関数だ。1つ目の引数「文字列」には、置き換えの対象となる元の文字列を指定する。2つ目の引数「開始位置」には、置き換えたい箇所の先頭位置を、先頭を1とする数値で指定する。3つ目の引数「文字数」には、置き換えたい文字数を数値で指定する。4つ目の引数「置換文字列」には、置き換え後の文字列を指定する。
このREPLACE関数を利用し、拠点名から地区名だけを取り出す。A2セルの「東京本社」から地区名の「東京」だけをB3セルに取り出す数式を先に提示しよう。
---------------------------------------------
=REPLACE(文字列, 開始位置, 文字数, 置換文字列)
------ -------- ------ ----------
( A2, LEN(A2)-1, 2, "" )
---------------------------------------------
大まかな仕組みは「末尾2文字を空の文字列に置き換える」というものだ。「東京本社」の末尾2文字「本社」が空の文字列に置き換われば、「東京」だけが残る。
引数「文字列」には元の文字列が格納されたA2セルを指定している。
引数「開始位置」には、「本社」が始まる位置を指定するため、「LEN(A2)-1」を指定している。LEN関数は引数に指定した文字の文字数を取得する関数だ。A2セルのデータは「東京本社」なので「LEN(A2)」は4が得られる。「東京本社」の中で3～4文字目の「本社」を置き換えたいということは、末尾の2文字を置き換えるのと同じことであり、末尾の2文字の開始位置は文字数から1を引けば得られる。したがって、引数「開始位置」には「LEN(A2)-1」を指定すればよいことになる。
引数「文字数」には、末尾2文字分を置き換えたということで2を指定する。引数「置換文字列」には、空の文字列「""」を指定している。このB2セルの数式をオートフィルなどでB6セルまでコピーすれば、図2のように地区名だけを取り出せる。
図2
また、もしB2～B6セルに「地区名＋「エリア」」という体裁の文字列を作りたければ、引数「置換文字列」に文字列「エリア」を指定すればよい（図3）。
----------------------------------
=REPLACE(A2,LEN(A2)-1,2,"エリア")
----------------------------------
図3
また、例えば、「博多支店」というデータがセルに入力されており、「支店」を「支社」に置換したいなら、以下のように引数を指定すればよい。
------------------------
=REPLACE(A6,3,2,"支社")
------------------------
引数「文字列」にはA6の「博多支店」を指定している。置き換えたい「支店」はこの文字列「博多支店」の先頭から3つ目の位置から始まるので、引数「開始位置」には3を指定する。引数「文字数」には、「支店」という2文字を置換したいので2を指定する。最後の引数「置換文字列」には、置き換え後の文字列である「支社」を指定している。この数式の結果として、文字列「博多支社」が得られる。
図4
引数「文字数」を0にすれば挿入も！
REPLACE関数の応用ワザとして、指定した箇所に文字列を挿入することも可能だ。例えば、郵便番号として7桁の数値が単に並んでいるとする。その3つ目と4つ目の数値の間に「-」（半角のハイフン）を挿入したいとする。
今回は図4のように、A2～A4セルにハイフンなしの郵便番号が入力されており、隣のB列にハイフンを挿入した郵便番号を別途作成したいとする。
図5
B2セルなら、以下のようにREPLACE関数を入力すればよい。
----------------------
=REPLACE(A2,4,0,"-")
----------------------
引数「開始位置」には、4つ目の数値の前にハイフンを挿入したいので、4を指定した。ポイントは引数「文字数」に0を指定していることだ。4つ目の文字の前という位置で、“0文字分”を「-」（引数「置換文字列」に指定）に置換することで、「-」を挿入する結果となる。置換する文字数を0にするという特殊な指定をすることで、このような挿入が可能となるのだ。
このB2セルの数式をオートフィルなどでB4セルにコピーすれば、図5のようにハイフンありの郵便番号を作成できる。
図6
このようにREPLACE関数を使えば、文字列の置き換えはもちろん、抽出や挿入までもできる。既存の文字列を加工して別の文字列を効率よく作りたい場合に、うまく活用しよう。
その他の【Excel】を見る
立山秀利（たてやま・ひでとし）
カーナビのソフトウェア開発、Webプロデュース業務を経て、現在は、システムやネットワーク、Microsoft Officeを中心に執筆中。 主な著書に『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』などがある。
（監修：日経BPコンサルティング）