Excel関数で文字列を自在に操るワザ

こんにちは、釈和（シャカズ）です。

突然ですが、業務の中ではCSVやいろいろなバージョンのエクセルなどのソフトから、
データを取り込み利用することは多々あります。

ところが、こちらの希望通りにそろっているデータではないことも少なくありません。

苗字と名前の間に全角のスペースを入れて揃えたいとか、
逆にスペースを取り除いて揃えたいとかいろいろあります。

とはいえ、データをひとつひとつ手作業で直していくなんてのはナンセンス。

エクセルには文字列を自在に操ることができる関数があります。

これを使ったワザを覚えておくと、
どんなデータでもすっきり見やすく差込印刷などスッキリと利用することができます！

使い方、できるだけわかりやすくご紹介します。

アイキャッチ画像：ぱくたそ

 

アルファベットの大文字小文字変換

同じ項目にアルファベットの大文字・小文字が混在してしまう場合があります。

ひとつずつ修正すればいいのですが、
データが多いと修正も大変です。

そこで、セルに入力されている文字に対してすべて小文字に、
または大文字に変換できる関数を使います。

使用する関数は、UPPEE関数、LOWER関数です。

対象となるのはアルファベットだけです。

関数の挿入方法はこちら

大文字にする

UPPER関数は文字列に含まれるアルファベットをすべて大文字に変換します。

＝UPPEE(文字列)

文字列は文字列のあるセルを指定します。

対象セルの文字列（A1）	使った関数	表示結果
Abc	=UPPER(A1)	ABC

 

小文字にする

LOWER関数は文字列に含まれるアルファベットをすべて小文字に変換します。

＝LOWER(文字列)

対象セルの文字列（A1）	使った関数	表示結果
Abc	=LOWER(A1)	abc

文字列は文字列のあるセルを指定します。

 

文字列から特定の文字を抽出する

セルにある文字列から指定した文字を取り出す際に使う関数には、

LEFT関数、RIGHT関数、MID関数、SUBSTITUTE関数があります。

 

文字列の先頭（左）から抽出する

LEFT関数は文字数を「文字の数」でカウントして取り出す関数です。

対象のセルにある文字列の先頭（左）から文字数を指定して文字を取り出します。

＝LEFT(対象セル,文字数)

対象セルの文字列（A1）	使った関数	表示結果
426-1234	=LEFT(A1,3)	426

 

文字列の先頭（右）から抽出する

RIGHT関数は文字数を「文字の数」でカウントして取り出す関数です。

対象のセルにある文字列の先頭（右）から文字数を指定して文字を取り出します。

＝RIGHT(対象セル,文字数)

対象セルの文字列（A1）	使った関数	表示結果
426-1234	=RIGHT(A1,4)	1234

 

文字列の中間を抽出する

MID関数も文字数を「文字の数」でカウントして取り出す関数です。

対象のセルにある文字列の中間から文字数を指定して文字を取り出します。

＝MID(対象セル,スタートの文字位置,文字数)

対象セルの文字列（A1）	使った関数	表示結果
426-1234	=MID(A1,4,1)	–

=MID(A1,4,1)は、「A1にあるセルの文字列の4番目の1つを取り出す」ので「-」が表示されます。

 

文字列の指定した文字以外を抽出する

文字を置換できるSUBSTITUTE関数を使います。

対象のセルにある文字列から指定した文字以外を取り出したり、
削除することができます。

＝SUBSTITUTE(対象のセル,元の文字列,置換後の文字列)

対象セルの文字列（A1）	使った関数	表示結果
426-1234	=SUBSTITUTE(A1,”-“,”★”)	426★1234
426-1234	=SUBSTITUTE(A1,”-“,””)	4261234

 

スペースを挿入する

姓と名の間に空白（スペース）を入れる場合に、
MID関数とLEN関数を組み合わせて使います。

LEN関数は文字の長さ（文字数）を求める関数です。

姓が”2文字”の場合を例にし、”全角スペース”を挿入します。

＝MID(対象セル,スタートの文字位置,文字数)

＝LEN(対象セル)

対象セル

“LEFT関数“や”RIGHT関数“の組み合わせでも同様な結果が求められます。

対象セルの文字列 （A1）	使った関数	表示結果
山田太郎	=MID(A1,1,2)	山田
山田太郎	=LEN(A1)	4
山田太郎	=MID(A1,3,LEN(A1))	太郎
山田太郎	=MID(A1,1,2)&”　”&MID(A1,3,LEN(A1))	山田　太郎
山田太郎	=MID(A1,1,2)&”　”&MID(A1,3,100))	山田　太郎
山田太郎	=LEFT(A1,2)&”　”&MID(A1,3,LEN(A1))	山田　太郎
山田太郎	=LEFT(A1,2)&”　”&RIGHT(A1,LEN(A1)-2)	山田　太郎

 

文字数をカウントするLEN関数を使って組み合わせる点がポイントになります。

MID(A1,3,LEN(A1))の、便宜上LEN関数を使っていますが、
“100”などのように大きな数字を入力してもＯＫです。

RIGHT(A1,LEN(A1)-2)は、A1セルにある文字列の全体の文字から姓の2文字を差し引く、
つまりは「右側から文字全体から姓の2文字を差し引いた2文字、つまり太郎を返す」となります。

 

抽出したい文字数が変動する場合

「神奈川県横浜市」などの住所から「神奈川」と「横浜市」など、
抽出したい文字数が変動する場合に使う関数です。

抽出したい文字が変動するとは（ここでは神奈川県と静岡県）では、
“県”の文字を抽出しようとすると前から4番目、3番目と変動するということです。

ここでは、FIND関数をLEFT関数やRIGHT関数と組み合わせて使います。

 

住所から県名だけを抽出する

文字列の中間を抽出する

MID関数も文字数を「文字の数」でカウントして取り出す関数です。

対象のセルにある文字列の中間から文字数を指定して文字を取り出します。

＝FIND(検索文字列,対象,開始位置)

開始位置の入力は省略可能です。

FIND関数で得られる結果とLEFT関数との組み合わせで得られる結果を順に並べてみました。

対象セルの文字列（A1）	使った関数	表示結果
神奈川県横浜市	=FIND(“県”,A1)	4
神奈川県横浜市	=LEFT(A1,FIND(“県”,A1)-1)	神奈川

“県”の文字が文字列のはじめから4番目にあることをFIND関数で求めることができます。

FIND(“県”,A1)-1)では、4－1＝3の結果が表示されます。

つまり、LEFT関数で対象セルのA1の左から3番目の文字列を返すので”神奈川”が表示されます。

 

住所から県名を除いた文字だけを抽出する

「神奈川県横浜市」の県名を除いた文字だけを抽出する方法でｓ。

RIGHT関数、LEN関数、FIND関数を組み合わせて使います。

対象セルの文字列（A1）	使った関数	表示結果
神奈川県横浜市	=RIGHT(A1,LEN(A1)-FIND(“県”,A1))	横浜市

A1セルにある文字列「神奈川県横浜市」の文字数は、LEN(A1)=7となります。

A1の文字列に含まれる”県”は先頭から何番目にあるかFIND(“県”,A1)=4で求められます。

神奈川県横浜市（7）－県までの数（4）＝３をRIHT関数で右から3番目＝”横浜市”となります。

 

住所から都道府県名を除いた文字だけを抽出する

せっかく用意できた住所のリストから、
「都道府県」はいらないなんてこともあります。

そこで、住所リストから「都道府県」とそれ以下に分ける方法です。

ここでは、IF関数、MID関数、LEFT関数を組み合わせて使います。

 

東京都の「都」は左から③番目に登場する①文字です。⇒　=MID(A2,3,1)

この=MID(A2,3,1)=「都」が成立するならば左から③番目を返す。⇒LEFT(A2,3)

 

=IF(MID(A2,3,1)=”都”,LEFT(A2,3),””)

 

となり、以上で「東京都」を抽出できました。

 

同じように北海道の「道」、大阪府の「府」を、
IF関数で順次条件を繰り返して上記の式を使います。

 

=IF(MID(A2,3,1)=”都”,LEFT(A2,3),IF(MID(A2,3,1)=”道”,LEFT(A2,3),””))

=IF(MID(A2,3,1)=”都”,LEFT(A2,3),IF(MID(A2,3,1)=”道”,LEFT(A2,3),IF(MID(A2,3,1)=”府”,LEFT(A2,3),””)))

 

神奈川県と静岡県は同じ「県」ですが、
④番目と③番目に「県」が登場するのでIF関数で条件を分けてあります。

 

=IF(MID(A2,3,1)=”都”,LEFT(A2,3),IF(MID(A2,3,1)=”道”,LEFT(A2,3),IF(MID(A2,3,1)=”府”,LEFT(A2,3),IF(MID(A2,3,1)=”県”,LEFT(A2,4),LEFT(A2,3)))))

 

この式をドラッグして「都道府県」を抽出することができました。

 

ところで、”県”が4番目に登場するのは”神奈川県”のみですから、
シンプルに考えると次のような式となります。

=IF(MID(A2,4,1)=”県”,LEFT(A2,4),LEFT(A2,3))

コピペして住所から都道府県名を抽出することができますね。(^<^)

 

 

次に、「東京都港区台場1-5」から「東京都」を除いた「港区台場1-5」を抽出します。

RIGHT関数とLEN関数を組み合わせて使います。

=RIGHT(A2,LEN(A2)-LEN(B2))

LEN関数でA2の住所の文字数からB2（東京都）を除いた文字を右から抽出するという式になります。

ドラッグをして式をコピペします。

元の住所から「都道府県」を抽出して、
そのあとの住所と分けることができました。

 

今回のまとめ

不揃いの文字列を揃えたり取り出したり削除したりと、
文字列を操作する関数をご紹介しました。

• UPPER関数・・・アルファベットの小文字を大文字に変換します。
• LOWER関数・・・アルファベットの大文字を小文字に変換します。
• LEFT関数・・・対象のセルにある文字列の左先頭から文字数を指定して取り出す。
• RIGHT関数・・・対象のセルにある文字列の右先頭から文字数を指定して取り出す。
• MID関数・・・対象のセルにある文字列の中間から文字数を指定して文字を取り出す。
• SUBSTITUTE関数・・・文字列から指定した文字以外を取り出す、削除する。
• LEN関数・・・文字列の長さ（文字数）を返します。
• FIND関数・・・文字列の中から指定した文字を取り出します

これらの関数式の組み合わせで、
抽出したい文字数が変動する場合などでも結果を表示することができました。

また、文字を返す関数、文字数など数字を返す関数と、
それぞれの特徴を理解するといろいろと使えるかと思います。

いかがでしたでしょうか？

 

最後まで読んでくださりありがとうございます。人気ブログランキングに参加中。こちらクリックして頂けましたらうれしく思います。
↓↓↓

人気ブログランキング