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

住所の丁目、番を漢数字から算用数字に変換し、ハイフンでつなぐ「一丁目二番」→「1-2」

使用関数:FIND、MID、SUBSTITUTE、IFERROR、LEFT、LEN

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

目次


SUBSTITUTE関数で「丁目」をハイフンに変換

下図のE列「住所2」が作業の元データになります。
漢数字のデータ

最初に「丁目」をハイフンに置換し、「番」を消しましょう。
右隣のF2セルを選択して、文字列操作関数のSUBSTITUTEを組み立てます。

ダイアログで引数「文字列」に「番」を空白に置き換える数式を入れ子します。
SUBSTITUTE(E2,"番","")
検索文字列」に「"丁目"」を指定。
置換文字列」に半角ハイフン「"-"」を指定。
SUBSTITUTE関数の引数ダイアログボックス
最終的な数式は「=SUBSTITUTE(SUBSTITUTE(E2,"番",""),"丁目","-")」です。
数式を下までオートフィルコピーします。
丁目をハイフンに変えた列
これを1文字ずつ切り分けながら数字を変換させますが、下準備として、この「ハイフン番地」の最大文字数を確認しておきます。LEN関数で文字数を数え、MAX関数で最大値を求めます。
LEN関数で文字数を数え、MAX関数で最大値を求める
「ハイフン番地」の切り分け列を最大文字数と同じだけ増設し、変換の完了した番地を表示させる列も作っておきます。
ここでは最大文字数が「5」なので5列を増やし、列見出しに1~5と番号を入れておきます。この列見出しの番号は関数の引数でセル参照します。
作業に必要な列を増設

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

切り分け列の最初のG2セルを選択して数式を組み立てていきます。

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

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

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

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

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

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

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

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

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

前章で切り出した漢数字を算用数字に変換するのに、FIND関数を使います。

FINDは特定の文字が指定した文字列の何番目に位置するかを数値で返します。漢数字を位置番号に置き換えるわけですね。

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

最初の引数「検索文字列」は対象範囲から探す文字を指定します。
ここではMID関数で切り分けた1文字を指定しますから、前章で組み立てた数式「MID($F2,G$1,1)」を入れ子します。引数の右横の結果が「"三"」になっていることを確認します。(※数式を入れ子する時は「=」を除外するのを忘れないで下さい)
FIND関数の引数「検索文字列」にMID関数の数式を入れ子

次の引数「対象」には漢数字の「〇」から「九」までを「"〇一二三四五六七八九"」と入力します。
最後の引数「開始位置」は「対象」の文字列のどの位置から検索を始めるかを数値で指定します。先頭から開始する場合は省略できますので、ここは指定なしでOK。
<FIND関数の引数「対象」に漢数字の「〇」から「九」までを指定
FINDは「検索文字列」の「三」が「〇一二三四五六七八九」の何番目にあるかを数値で返してくれるのですが、このままだと「〇」が「1」となり、意図した数値より1つ多い数字が返ってきてしまいます。
FIND関数で返る数字が意図した数値より1つ多い
そこで、この数式から1を引きます。数式バーで末尾に「-1」を入力。
FIND関数の数式の末尾に「-1」を入力
数式は「FIND(MID($F2,G$1,1),"〇一二三四五六七八九")-1」となり、数字同士が等しく対応するようになりました。
数字同士が等しく対応

IFERROR関数でハイフンをそのまま表示

前章の数式を下と右にフィルコピーすると、ハイフンの入ったセルにエラーが出るので、これを利用してIFERROR関数でハイフンをそのまま表示させましょう。
▶Excel2013からIFERROR関数を使う。ISERROR不要

ダイアログでの指定は以下の通りです。
引数「」に、前章の数式「FIND(MID($F2,G$1,1),"〇一二三四五六七八九")-1」を入力。
引数「エラーの場合の値」には、半角ハイフンを「"-"」のように入力します。
IFFEROORの関数の引数ダイアログボックス
数式を手打ちするなら、「=IFERROR(FIND(MID($F2,G$1,1),"〇一二三四五六七八九")-1,"-")」となります。

この数式をすべての範囲にフィルコピーすれば、漢数字が算用数字に置き換わり、ハイフンはそのまま表示されます・・・が、
漢数字が算用数字に置き換わり、ハイフンはそのまま表示
一見して分かるように、空白のセルに「0」が表示されています(下図グレーのセル)。FIND関数では空白は「0」と認識されるためですが、実際の「0」のデータと混在しているので始末に困りますね。
FIND関数では空白は「0」と認識される
これは次章で行う文字列の連結作業時に、元データの「ハイフン番地」の文字数に応じて連結させるようにします。

仕上げ:文字列を連結

元データ「ハイフン番地」の文字数が「3」なら、左から列見出しの3までを連結させるには、LEFT関数を使います。

「連結番地」の最初のセルを選択して、「数式」タブ「関数ライブラリ」「文字列操作▼」から「LEFT」をクリック。
関数ライブラリの文字列操作からLEFTをクリック
LEFT関数の引数は「文字列」と「文字数」の2つ。文字列の左端から文字数分だけ取り出してくれます。
文字列」は1文字ずつ切り分けた5列分のセル範囲を連結させた文字列を指定することになります。「&」でつなげるか、CONCAT関数(またはCONCATENATE)で連結させるか、慣れてる方式を使って下さい。
文字数」はLEN関数で元データの「ハイフン番地」のセルから求めます。
LEFT関数の引数ダイアログボックス
数式は「=LEFT(G2&H2&I2&J2&K2,LEN(F2))
連結させた文字列の左端から、元データの文字数分の文字を取り出す」
CONCAT関数を使った場合は「=LEFT(CONCAT(G2:K2),LEN(F2))」となります。

下までフィルコピーして完了です。作業に係る列は非表示にしておきましょう。
連結したデータ

▶住所の番地を算用数字から漢数字に変換する「1-2」→「一丁目二番」
【関数を使った文字列操作】▶氏名を入力すると自動で「姓」「名」を分割(LEFT・RIGHT・LEN)・フリガナ表示(PHONETIC)


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