よつ葉ナビ

WebサービスやPCソフトの使い方、日々思うことを綴るサイトです

エクセルでアルファベットを削除する方法:関数とマクロの両方を紹介

      2017/12/07

エクセルで、ある文字列からアルファベット(英字)のみを全て削除する方法 を紹介します。例えば、

あいうabえcおあいうえお

のようにアルファベットのみを綺麗に取り除くことができます。

「標準関数を組み合わせる方法」「マクロを使う方法」の2つを紹介します。

※アルファベットを全て削除するのではなく、特定のアルファベットのみを削除したい場合(例えば「ABCA123」→「BC123」といったように「A」のみを削除したい場合)は、「特定の文字を削除する」という記事をご覧下さい。

1: 標準関数を組み合わせる方法

UPPER関数、LOWER関数、MID関数などを組み合わせてアルファベットを取り除く方法です。

はじめに

エクセルには、標準関数の中に「アルファベットのみを削除する関数」がありません。ですので、既存の関数を上手く組み合わせてアルファベットを取り除きます

複数の関数を組み合わせなくてはならないため、少々式が長ったらしくて複雑に見えますが、やっていることはとても単純です。

式をコピペするだけで使えるようにしましたので、そのままエクセルのシートに貼り付けてお使い下さい(一部式の修正が必要な場合もありますので、詳しくは下の補足をお読み下さい)。

やり方(具体例)

例として、A1に入力した「あいabうc」という文字列からアルファベットを全て削除して「あいう」にします。

▲完成例

. まず、式が長いので、見やすくするために式の入力欄をドラッグで引き伸ばしておきましょう。

▲式の入力欄を引き伸ばしておくと分かりやすい

. B1に以下の式を貼り付けてエンターを押します。

=
IF(EXACT(MID(UPPER(A1),1,1),MID(LOWER(A1),1,1)),MID(A1,1,1),"")&
IF(EXACT(MID(UPPER(A1),2,1),MID(LOWER(A1),2,1)),MID(A1,2,1),"")&
IF(EXACT(MID(UPPER(A1),3,1),MID(LOWER(A1),3,1)),MID(A1,3,1),"")&
IF(EXACT(MID(UPPER(A1),4,1),MID(LOWER(A1),4,1)),MID(A1,4,1),"")&
IF(EXACT(MID(UPPER(A1),5,1),MID(LOWER(A1),5,1)),MID(A1,5,1),"")&
IF(EXACT(MID(UPPER(A1),6,1),MID(LOWER(A1),6,1)),MID(A1,6,1),"")&
IF(EXACT(MID(UPPER(A1),7,1),MID(LOWER(A1),7,1)),MID(A1,7,1),"")&
IF(EXACT(MID(UPPER(A1),8,1),MID(LOWER(A1),8,1)),MID(A1,8,1),"")&
IF(EXACT(MID(UPPER(A1),9,1),MID(LOWER(A1),9,1)),MID(A1,9,1),"")&
IF(EXACT(MID(UPPER(A1),10,1),MID(LOWER(A1),10,1)),MID(A1,10,1),"")

※対象文字列が10文字以内の場合(10文字以上の場合は補足を参照)

▲式を貼り付けした様子。
①B1を選択 → ②数式バーをクリック → ③式を貼り付けてエンター

補足1: セル番地の変更について

式を「コピー」して「貼り付け」でOKですが、赤色の「A1」の部分だけ対象の文字列が入っているセル番地に変えてください。

補足2: 10文字以上の場合

アルファベットを取り除く前の対象文字列が10文字以上の場合は、行を増やして下さい。例えば、対象文字列の長さが1文字~15文字の範囲なら、式を15行に増やします。

=
IF(EXACT(MID(UPPER(A1),1,1),MID(LOWER(A1),1,1)),MID(A1,1,1),"")&
IF(EXACT(MID(UPPER(A1),2,1),MID(LOWER(A1),2,1)),MID(A1,2,1),"")&
IF(EXACT(MID(UPPER(A1),3,1),MID(LOWER(A1),3,1)),MID(A1,3,1),"")&
IF(EXACT(MID(UPPER(A1),4,1),MID(LOWER(A1),4,1)),MID(A1,4,1),"")&
IF(EXACT(MID(UPPER(A1),5,1),MID(LOWER(A1),5,1)),MID(A1,5,1),"")&
IF(EXACT(MID(UPPER(A1),6,1),MID(LOWER(A1),6,1)),MID(A1,6,1),"")&
IF(EXACT(MID(UPPER(A1),7,1),MID(LOWER(A1),7,1)),MID(A1,7,1),"")&
IF(EXACT(MID(UPPER(A1),8,1),MID(LOWER(A1),8,1)),MID(A1,8,1),"")&
IF(EXACT(MID(UPPER(A1),9,1),MID(LOWER(A1),9,1)),MID(A1,9,1),"")&
IF(EXACT(MID(UPPER(A1),10,1),MID(LOWER(A1),10,1)),MID(A1,10,1),"")&
IF(EXACT(MID(UPPER(A1),11,1),MID(LOWER(A1),11,1)),MID(A1,11,1),"")&
IF(EXACT(MID(UPPER(A1),12,1),MID(LOWER(A1),12,1)),MID(A1,12,1),"")&
IF(EXACT(MID(UPPER(A1),13,1),MID(LOWER(A1),13,1)),MID(A1,13,1),"")&
IF(EXACT(MID(UPPER(A1),14,1),MID(LOWER(A1),14,1)),MID(A1,14,1),"")&
IF(EXACT(MID(UPPER(A1),15,1),MID(LOWER(A1),15,1)),MID(A1,15,1),"")

行を増やした場合は、UPPER関数、LOWER関数、MID関数の中に含まれる数字を、行数に対応したものに変更します。例えば、11行目なら数字を11に、12行目なら12にします。上の式の例で言うと、青色の数字がそれに該当します。

また、10行目に「&」を加え、最後の行だけは式の後ろの「&」を取り除いて下さい。「&」は文字列の結合の意味なのですが、一番最後は何も文字を結合しないため記述しません。

同じ要領で、50文字、100文字と増やすことができます。100行にした場合、1~100文字まで処理することが可能です。

. 複数行に同じ処理を適用させたい場合は、セルB1の式をドラッグしましょう。

機能の説明

この式の機能などについて箇条書きで簡単に説明します。

  • 「大文字/小文字」「半角/全角」の総計104種類のアルファベットを取り除く
  • アルファベットが表れる位置が分からなくても削除できる
  • 10行なら10文字以内、20行なら20文字以内、といったように追加した行数に応じて文字列を処理できる

つまり、「あaAアaA亜」→「あア亜」のように、「ランダム」に混ざっている「大文字/小文字」「半角/全角」のアルファベットを綺麗に削除できるというものです。

式の解説

読みたい方だけ読んで下さい。

「aあBcいう」→「あいう」を例にして、式の仕組みについて解説します。式は、主に3つに分解することができます。

IF( EXACT(MID(UPPER(A1),1,1), MID(LOWER(A1),1,1)), MID(A1,1,1), "" )&

ピンクのマーカー部分から説明します。

 

まず、UPPER関数でA1(aあBcいう)に含まれるアルファベットを全て「大文字」にします。

UPPER(A1)
---
"AあBCいう"

UPPER関数は、アルファベットを大文字にする関数です。これによって「AあBCいう」という文字列が完成します。

 

続いて、同じように、LOWER関数でA1(aあBcいう)に含まれるアルファベットを全て「小文字」にします。

LOWER(A1)
---
"aあbcいう"

LOWER関数は、アルファベットを小文字にする関数です。これによって「aあbcいう」という文字列が完成します。UPPER関数とLOWER関数は、アルファベットにしか適用されない関数なので、それ以外の文字には変化が起こりません。

 

アルファベットを全て大文字にした「AあBCいう」と、全て小文字にした「aあbcいう」を、MID関数でそれぞれ1文字抜き出します。

MID( "AあBCいう", 1, 1 ), MID( "aあbcいう", 1, 1 )
-------------------------------------
----------"A"---------------------------"a"

MID関数とは、指定した文字列から指定した数だけ文字を抜き出す関数です。

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

これによって、「A」と「a」が取り出されました。

 

先程③で取り出した「A」と「a」をEXACT関数で比較します。

EXACT( "A", "a" )

EXACT関数は、2つの文字を比較して、等しいならTRUE(真)を返し、違っていたらFALSE(偽)を返す関数です。

さて、ここからが面白いところです。EXACT関数は、大文字と小文字は別の文字であると判定します。UPPER関数とLOWER関数は、アルファベットを大文字や小文字にする関数なので、アルファベット以外の文字には適用しませんでした

つまり、EXACT関数で2つの文字を比較する際、FALSE(相違である)ということは、片方が大文字でもう片方が小文字であるという事であり、調べた文字がアルファベットであると断定できます

もしこれが、ひらがなの「あ」なら「EXACT( "あ", "あ" )」となり、TRUE(同じ文字である)と判定されます。アルファベットの「A」なら「EXACT( "A", "a" )」となり、FALSE(異なる文字である)と判定されます。要は、

  • EXACT関数が「TRUE」を返せばその文字はアルファベットではない
  • EXACT関数が「FALSE」を返せばその文字はアルファベットである

と判定できるわけです。

 

最後にIF関数を使って、「アルファベットの場合の処理」と、「アルファベットではない場合の処理」をそれぞれ記述して完成です。

IF( FALSE, MID(A1,1,1), "" )&

IF関数は、ピンクマーカー部分がTRUEならオレンジのマーカー部分を処理し、ピンクマーカー部分がFALSEなら緑のマーカー部分を処理する関数です。「IF = もしも~ならば」と訳せるように、分岐処理を行えます。

IF( 論理式, TRUEの処理, FALSEの処理 )

TRUEの場合は、「アルファベットではない」ということなので、MID関数で1文字抜き出してそのまま表示します(オレンジマーカー部分)

逆にFALSEの場合は、「アルファベットである」ということなので、「""」と記述して空の文字を表示します(緑のマーカー部分)。空の文字の表示とは、すなわち文字の削除のことであり、これによってアルファベットを削除することができます。

ここまでが、「先頭の1文字がアルファベットかどうか」を調べるまでの流れです。この処理を1行分の式で行い、2文字目は2行目、3文字目は3行目…、といった感じで同様の処理を行います。10行ならこれを10回繰り返します。

つまり、「aあBcいう」なら、「a」→「あ」→「B」→「c」→「い」→「う」の順にアルファベットかどうかを判定していき、最終的に「a」「B」「c」を取り除いた「あ」「い」「う」がセルに表示されます。

ただ、このままだと文字がくっついておらずバラバラなので、「&」を使って文字を結合します。

IF( FALSE, MID(A1,1,1), "" )&
IF( TRUE, MID(A1,2,1), "" )&
IF( FALSE, MID(A1,3,1), "" )&
・・・

これらの一連の処理のイメージを図で表すと以下のようになります。

▲イメージ図。UPPER関数とLOWER関数で作った2つの文字列を、EXACT関数で先頭から順番に1文字ずつ比較していき、アルファベットが見つかれば弾く。1文字ずつ抜き出す為にMID関数を使う。

2: マクロを使う方法

マクロとは?

マクロとは、一連の処理をひとまとめにする機能のことです。難しそう!と思うかもしれませんが、実は意外と簡単です。

この方法を試す場合は、プログラムを実行させるので、手違いでファイルが壊れたりしても大丈夫なように、念のため元のファイルをバックアップしてから行って下さい。

ちなみに、マクロが含まれたファイルは、通常ファイルとは拡張子が異なります。

▲左がマクロ有りのファイル、右がマクロ無しのファイル

マクロを使う方法のメリット

マクロを使うメリットですが、「標準関数を組み合わせる方法」よりも無駄な処理が省かれているので高速です。

対象文字列があまりにも長くて処理が重い場合には「マクロを使う方法」を試してみると良いでしょう。

また、上で紹介した方法とは違って、文字列の長さによって式の中身をいじったりする必要も無いので、非常に楽です(プログラム内で文字列の長さを調べてその長さによって処理回数を決めているため)。

例えば、対象文字列が10文字でも20文字でも同一のプログラムコードで動作します。

手順

. ①「開発」タブ → ②「Visual Basic」をクリックします。

. ①「Sheet(Sheet1)」をダブルクリック → ② 以下のソースコード(プログラム)を入力欄に貼り付けます。

▼ ソースコード

. ① 左上の「保存マーク」をクリック → ② マクロが使える専用のファイル形式で保存するので「いいえ」をクリックします。

. ファイルの種類で「Excel マクロ有効ブック(*.xlsm)」を選択してから保存します。

▲マクロ有効のファイルが作成される。以後こちらを利用する。

. 左上の「ワークシート表示ボタン」を押して、ワークシートに戻ります。

. ① アルファベットを取り除きたいセルを選択 → ② 開発タブにある「マクロ」をクリックします。

. ① マクロ一覧が表示されるので使いたいマクロをクリックで選択 → ②「実行」を押して処理を実行します。

選択範囲の文字列をそのまま編集するので、元の値を残したい場合は、元の値を切り取って別の列に貼り付けてから処理を実行して下さい。
行が多い場合は、「Ctrl+Shift+↓」で全選択してから実行すると楽です。

. アルファベットが綺麗に取り除かれました。

以後、開発タブにあるマクロボタンを押していつでもマクロを実行できます。一度マクロを作ってしまえば、それ以降はボタンをポチポチするだけでアルファベットを取り除けるのでとても楽です。

xlsmファイルを開き直すと、「セキュリティの警告」という黄色い見出しが表示されます。

これは、マクロが含まれたファイルを開くときに表示されるものなので、「コンテンツの有効化」をクリックしてマクロを使えるようにしておきましょう。

マクロを使ってアルファベットを削除する方法については以上です。

その他の方法

正規表現

上の「標準関数を組み合わせる方法」と「マクロを使う方法」以外だと、「正規表現」を使う方法があります。

正規表現とは、文字列操作ができる非常に強力な仕組みのことです。正規表現を使えば、アルファベットのみを綺麗に削除するといったことが容易にできます。

ただ、エクセルには正規表現が使える機能が標準では搭載されていないので、正規表現を扱える「テキストエディター」「専用のツール」に一旦データを貼り付けてアルファベットを削除した後、エクセルに貼り付けて戻す、という方法を取ることになります(非公式ではありますが、エクセルで正規表現を扱えるようにするアドインが一応あるようです)。

「正規表現ってなんだかよく分からない!」という方は、上で紹介した方法を使った方が楽だと思います。こんな方法もあるよ、ということで紹介させていただきました。

まとめ

エクセルは元々文字列を扱う機能が少ないため、少し応用的な内容での紹介となりました。まとめると以下のようになります。

標準関数を組み合わせる方法 多少処理が重いがマクロなどを一切使わずワークシート内だけで完結できる。
マクロを使う方法 一度設定すればボタンをポチポチするだけで使える。
処理に無駄が少なく高速に動作する。

やっていることの意味が理解できなくても、一応コピペでできるようにはしているので、アルファベットのみを削除したいときはこれらの方法を使ってみてください。

ちなみに、他にもやり方は色々あるのですが、上で紹介した方法より複雑であったり、似たような内容だったりするので、この記事では割愛します。

文字の削除に関しては、関連記事の「特定の文字を削除する」を読んでいただくと、より理解が深まると思います。

 - Excel