記録することによって見えてくるもの

ExcelのSUBSTITUTE関数で文字列を検索して他の文字列に置き換える

かわべ
WRITER
 
この記事を書いている人 - WRITER -
かわべ
生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)
<スポンサーリンク>  
 

ExcelのSUBSTITUTE関数は、文字列から指定の文字列を検索し、他の文字列に置き換える関数です。

SUBSUTITUTE関数_18

(使用Excel)2013

参考

参考 Office Support;SUBSTITUTE 関数


<スポンサーリンク>    
 

ExcelのSUBSTITUTE関数で文字を置き換える

Excelで文字列の置き換えをする場合、REPLACE関数、REPLACEB関数、SUBSTITUTE関数を使用しますが、SUBSTITUTE関数は、検索した文字を置き換える関数です。

※ REPLACE関数は、文字列の開始位置と、その開始位置からの文字数を指定して置き換える関数になります。(REPLACEB関数は文字数をバイト数で指定する関数です。)

 

SUBSTITUTE関数の記述方法

SUBSTITUTE関数は次のように記述します。

SUBSUTITUTE関数_11

文字列、検索文字列、置換文字列を省略することはできません。

(Office Support;SUBSTITUTE 関数より。背景色は筆者追記)

SUBSTITUTE 関数の書式には、次の引数があります。

文字列 必ず指定します。 置き換える文字を含む文字列を指定します。目的の文字列が入力されたセル参照を指定することもできます。

検索文字列 必ず指定します。 置換する文字列を指定します。

置換文字列 必ず指定します。 検索文字列を検索して置き換える文字列を指定します。

置換対象 省略可能です。 検索文字列に含まれるどの文字列を置換文字列と置き換えるかを指定します。 置換対象を指定した場合、検索文字列中の置換対象文字列だけが置き換えられます。 指定しない場合、検索文字列中のすべての文字列が置換文字列に置き換えられます。

 

SUBSTITUE関数の使用例1(セルの文字列を置き換える)

SUBSTITUTE関数の使用例として次のようなExcelのシートを用意しました。

SUBSUTITUTE関数_12

この表のC列にSUBSTITUTE関数を使って、B列の勘定科目名称から特定の科目名称を検索して、他の科目名称に変更するようにします。

SUBSUTITUTE関数_13

上の画像とおり、C列に次のような式を入力しました。

『=SUBSTITUTE(B2,E$2,G$2)』

この式は、B2セルを参照して、検索文字(E2セルの「消耗品費」)と一致する場合は、置換文字(G2セルの「備品消耗品」)に置き換えるという記述をしています。

これについては、IF関数を使っても似たような記述ができるかと思います。

 

SUBSTITUE関数の使用例2(セル内の文字列の空白を置き換える)

SUBSTITUTE関数の特徴(文字列から特定の文字列を検索する機能)を活かす使用例として文字列に含まれるスペースを探して削除するという例を記載しておきます。次のようなExcelの表を用意しました。

SUBSUTITUTE関数_14

先ほどの表との違いは、科目名称に半角スペースが含まれていることです。5文字の科目名称には半角スペースが含まれず、消耗品費や会議費、雑費など、5文字未満の科目名称には半角スペースが含まれています。(会計ソフトでは良く見かける光景です。)

SUBSTITUTE関数を使って、この半角スペースを取り除いてみます。ためしに、E2セルに半角スペースを入力し、G2セルに「!!!!!!!」と入力して置き換えてみました。E2セルに入力した半角スペースをちゃんと認識しているようです。

SUBSUTITUTE関数_15

 

次にG2セルに入力していた「!!!!!!!」を削除したところ、下の画像のとおりC列に半角スペースがなくなった科目名称が表示されました。

SUBSUTITUTE関数_16

ちなみにE2セルには半角スペースが入力されているため、全角スペースは認識しませんでした。上の画像の「雑    費」(半角スペースが4つ)を「雑  費」(全角スペースが2つ)に変更してみました。(下の画像)

SUBSUTITUTE関数_17

上の画像のとおり、E2セルで半角スペースを検索文字に指定しているため、全角スペースを認識せず、もとのB列の「雑  費」がそのままC列に表示されました。

※ AND関数やOR関数を使用して、検索文字の部分に複数の文字列を指定してみましたが、SUBSTITUTE関数1つで半角、全角、両方のスペースを取り除くことはできませんでした。

※ SUBSTITUTE関数を2つを使って半角、全角、両方のスペースを取り除くことができました。(セル番号を使用せず「””」を使って、半角スペース、全角スペースを記述しました。)

「=SUBSTITUTE(SUBSTITUTE(B2,” “,””),” ”,””)」

 


■□◆◇ 編集後記 ◇◆□■

今日も関東は不安定な天気です。関東の利根川水源のダムの貯水率がちょっと増加しているようですが、油断したらいけないんだろうな。節水。

 

Pocket
このエントリーを Google ブックマーク に追加
LINEで送る

この記事を書いている人 - WRITER -
かわべ
生誕:1969年 東京 生まれ 趣味:楽器演奏(St.Bass、E.Bass)     読書(ミステリー)     映画鑑賞(学生時代、映画館でアルバイト経験あり)

報酬のお見積り

法人の顧問報酬と決算報酬を、前期の売上実績当期の売上予測でお見積もりすることができます。(※)

LINK 報酬お見積もり【法人用】 web

お名前やメールアドレスの入力は必要ありません。ウェブ上で簡単にお見積もりができます。

※ オプション料金は別途となります。

Copyright© J-musu-no-blog , 2016 All Rights Reserved.