年賀状の住所録や顧客リストの住所一覧を利用して封筒に宛名印刷をする場合など、横書きの封筒の場合は「1，2，3」のような算用数字(アラビア数字)で表記しますが、縦書きの封筒の場合「一、二、三」のような漢数字で表記する必要があります。
数字を置換する方法は：
- 「セルの書式設定」を使う方法
- 直接置換する方法
- 「SUBSTITUTE関数」で置換する方法
など複数の方法があります。
しかし住所の中に含まれる数字となると、「セルの書式設定」を使うことは出来ません。
また、直接置換する方法は途中で置換を失敗してしまうと取り返しがつかないことになってしまうリスクがあることと、また数字に戻したいときはまた同じように置換する必要があったりと、現実的ではありません。
そこで今回は「SUBSTITUTE関数」で置換する方法に焦点を絞ってご紹介することといたします。
先に答えを言うと、「SUBSTITUTE関数」で下記のように記載します。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(A1),"１","一"),"２","二"),"３","三"),"４","四"),"５","五"),"６","六"),"７","七"),"８","八"),"９","九"),"０","〇")今回はこの関数の記載方法について詳しく解説していきます。
また、Excel2003以前など古いバージョンの場合は上記の関数ではうまく置換ができません。
このような場合の対処方法も併せてご紹介いたします。
エクセルで作成した住所録や宛先リストをワードを使って簡単に印刷する方法をこちらの記事でご紹介しています。合わせてご覧ください。↓↓↓
「SUBSTITUTE関数」の基本的な使い方
「SUBSTITUTE関数」の記載ルールは以下の通りです。
＝SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
例えば下図のように「A1」セルに算用数字の「1」を含む文字列が入力されているとします。
そして「A2」セルに以下のように入力します。
=SUBSTITUTE(A1,1,"一")
この状態で「エンター」キーを押すと、下図のように「1」の算用数字が「一」の漢数字に置換することができました。
「SUBSTITUTE関数」ですべての数字を置換する
しかしこの状態では「1」しか置換できていないのでネズミが「1234567890」匹になると
「ネズミが一234567890匹」と、算用数字と漢数字が混ざった表示になってしまいます。
これを解消するためには1～9までと0を同様に置換する必要があります。そこですべての数字を置換した数式がこちらです。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1","一"),"2","二"),"3","三"),"4","四"),"5","五"),"6","六"),"7","七"),"8","八"),"9","九"),"0","〇")この数式を「A2」セルに入れると
このようにすべての数字を数字を置換することができました。
全角数字が混ざっている場合
今ご紹介した方法は数字が半角数字になっている場合に限ります。
上記の例で、真ん中の「5」だけが全角数字になっていると下図のように全角の数字だけ置換することができません。
これを解消するためには「ASC関数」を使います。「ASC関数」は指定したセルの値を半角に変換する関数です。「ASC関数」を使った例が下記のとおりです。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC(A1),"1","一"),"2","二"),"3","三"),"4","四"),"5","五"),"6","六"),"7","七"),"8","八"),"9","九"),"0","〇")これで全角の数字が混ざっていてもすべての数字を置換することができました。
しかし、よく見ると「ネズミ」の部分まで半角になってしまっています。これは「ASC関数」が半角のある文字はすべて半角に変換してしまうためです。
もし、カタカナやアルファベットは全角にしておきたい場合は逆に全角にそろえることで全角のまま表示する事ができます。その場合は関数は以下のように変わります。こちらが冒頭でご紹介しました関数と同じものです。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(A1),"１","一"),"２","二"),"３","三"),"４","四"),"５","五"),"６","六"),"７","七"),"８","八"),"９","九"),"０","〇")分かりにくいですが、算用数字がすべて全角に変わっています。これを「A2」セルに入力すると
エラーになる場合
今エクセルを使っている方はほとんどが大丈夫だと思いますが、Excel2003までのバージョンでは上記の方法ですと7個までしか関数を重ねることができませんので、エラーになってしまいます。
その場合は1～5までと6～0までの2段階で関数を分けて記載する必要があります。
1段階目を「A2」セルに、2段階目を「A3」セルに入力します。
1段階目
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"一"),2,"二"),3,"三"),4,"四"),5,"五")2段階目
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"6","六"),"7","七"),"8","八"),"9","九"),"0","〇")すると下図のように「A3」セルに正しく表示させることができました。
まとめ
「SUBSTITUTE関数」を使うことで元のデータを変更することなく、漢数字に置換させることができます。
住所の中に数字が含まれている場合でもこの関数を使うことで下図のように漢数字に置換することができます。
この方法を使って業務の効率化を進めていってください。
コメント