Excel使いこなしの基礎と応用

NUMBERSTRING関数で住所の番地を算用数字から漢数字に変換する「1-2」→「一丁目二番」

使用関数:NUMBERSTRING、MID、IFERROR、LEFT、LEN

半角数字で入力してある住所データの所番地を漢数字に置き換える方法です。NUMBERSTRING関数を使って、「東京都○区○○1-2」という表記を「東京都○区○○一丁目二番」にします。逆に漢数字から算用数字にする方法

目次


文字を切り分けながら算用数字に置換える数式を構築

下図のE列「住所2」が漢数字に変換する元データになります。
漢数字に変換する元データ
下準備として、この「住所2」の最大文字数を確認しておきます。LEN関数で文字数を数え、MAX関数で最大値を求めます。
「住所2」の最大文字数を確認

「住所2」の切り分け列を最大文字数と同じだけ増設し、変換の完了した番地を表示させる列も作っておきます。
ここでは最大文字数が「5」なので5列を増やし、列見出しに1~5と番号を入れておきます。この列見出しの番号は関数の引数でセル参照します。
作業に必要な列を増設

まず、このセルに入れる数式の要素を文章にして羅列してみましょう。
「E2セルの入力値を1文字ずつ切り出す」「切り出された文字がハイフンなら、丁目に置換え」「切り出された文字が数字なら漢数字に置き換える」
3つの要素の内、最後の2つは「~なら、」というIF文になっていますから、論理式の関数を使うのはすぐに分かりますね。この場合はハイフンがエラーになるのでIFERROR関数で処理します。
組み立て直すと「E2セルの入力値を1つずつ切り出した文字を漢数字に置き換え、エラーが返ったら、丁目を表示」となり、「E2セルの入力値を1文字ずつ切り出す」と「算用数字を漢数字に置き換える」を実装する数式をまず構築し、最後にIFERRORで囲うという流れになるわけです。

MID関数で文字を切り分ける

「E2セルの入力値を1文字ずつ切り出す」のには、指定した位置から文字列を抽出するMID関数を使います。

「数式」タブ「関数ライブラリ」「文字列操作▼」から「MID」をクリック。
関数ライブラリ→文字列操作→MID
ダイアログボックスが開きます。引数は3つ。

最初の引数「文字列」には抽出元の範囲を指定します。ここでは「住所2」のセル「E2」をクリックして指定。F4キーを3回押して絶対列参照(列のみ固定)にしておきます。
MIDの引数「文字列」に抽出元の範囲を指定

次の引数「開始位置」には抽出を開始する位置情報を数値で指定します。
ここでは前章で増設した切り分け列の最初の列見出し「1」のある「F1」セルをクリックして指定。F4キーを2回押して絶対行参照(行のみ固定)にしておきます。
MIDの引数「開始位置」に抽出を開始する位置情報を数値で指定

最後の引数「文字数」は抽出する文字の数を数値で指定します。
ここでは1文字ずつの切り出しですから、「1」と入れます。
MIDの引数「文字数」に抽出する文字の数を数値で指定

最終的な数式は「=MID($E2,F$1,1)」となります。
指定文字列1文字目から1文字を抽出する」

NUMBERSTRING関数で算用数字を漢数字に置き換える

前章で切り出した算用数字をNUMBERSTRING関数で漢数字に変換します。

NUMBERSTRINGは数値を漢数字に変換する関数です。まさにそのものズバリ、ひねり無し。ただ、この関数はライブラリに格納されていないので、数式バーかセルに手入力する必要があるのです。

引数は2つだけで、シンプルです。

最初の引数「数値」は変換する数値。
ここではMID関数で切り分けた1文字を指定しますから、前章で組み立てた数式「MID($E2,F$1,1)」をコピペします。続けて半角カンマを入力。
NUMBERSTRING関数の引数「数値」にMIDの数式をコピペ

次の引数「形式」には変換後の漢数字の型を以下の「1~3」から選んで指定します。

形式 変換する漢数字の型
1 「一、二、三・・・」「十、百、千、万・・・」
2 「壱、弐、参・・・」「拾、百、阡、萬・・・」
3 「〇、一、二、三、四、五、六、七、八、九」

ここでは形式「3」を指定します。
NUMBERSTRING関数の引数「形式」に対応番号「3」を指定

数式は「=NUMBERSTRING(MID($E2,F$1,1),3)」となります。ちゃんと漢数字に置き換わったか確認しましょう。
漢数字に置き換わった

IFERROR関数でハイフンを丁目に置換え

前章の数式を下と右にフィルコピーすると、ハイフンの入るセルにエラーが出るので、これを利用してIFERROR関数でハイフンを「丁目」に置換えます。
▶Excel2013からIFERROR関数を使う。ISERROR不要

ダイアログでの指定は以下の通りです。
引数「」に、前章の数式「NUMBERSTRING(MID($E2,F$1,1),3)」を入力。
引数「エラーの場合の値」には、「"丁目"」と入力します。
IFERROR関数の引数ダイアログボックス
数式を手打ちするなら、「=IFERROR(NUMBERSTRING(MID($E2,F$1,1),3),"丁目")」となります。「切り出した数字を漢数字に置き換え、エラーになった場合は”丁目”と表示する」

この数式をすべての範囲にフィルコピーすれば、数値が漢数字に置き換わり、ハイフンは丁目に置き換わります・・・が、「丁目」多すぎ!
数値が漢数字に置き換わり、ハイフンは丁目に置き換わった
これは次章で行う文字列の連結作業時に、元データの「住所2」の文字数に応じて連結させるようにし、不要な「丁目」を始末します。

仕上げ:文字列を連結

「住所2」の文字数が「3」なら、左から列見出しの3までを連結させるには、LEFT関数を使います。

「漢数字住所2」の最初のセルを選択して、「数式」タブ「関数ライブラリ」「文字列操作▼」から「LEFT」をクリック。
関数ライブラリの文字列操作からLEFTをクリック
LEFT関数の引数は「文字列」と「文字数」の2つ。文字列の左端から文字数分だけ取り出してくれます。
文字列」は1文字ずつ切り分けた5列分のセル範囲を連結させた文字列を指定することになります。「&」でつなげるか、CONCAT関数(またはCONCATENATE)で連結させるか、慣れてる方式を使って下さい。
文字数」はLEN関数で元データの「住所2」のセルから求めます。ただ、「丁目」が2文字なので末尾に「+1」を入力します。
LEFT関数の引数ダイアログボックス
数式は「=LEFT(F2&G2&H2&I2&J2,LEN(E2)+1)」。この数式の末尾に「&"番"」を付け足して、住所表記を完成させます。
数式の末尾に&"番"を追加
最終の数式「=LEFT(F2&G2&H2&I2&J2,LEN(E2)+1)&"番"
連結させた文字列の左端から、元データの文字数分+1の文字を取り出して末尾に番を付ける
CONCAT関数を使った場合は「=LEFT(CONCAT(F2:J2),LEN(E2)+1)&"番"」となります。

下までオートフィルコピーして完了です。作業に係る列は非表示にしておきましょう。
数値の漢数字への変換が完了
▶住所の丁目、番を漢数字から算用数字に変換する
【関数を使った文字列操作】▶氏名を入力すると自動で「姓」「名」を分割・フリガナ表示


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。