住所の漢数字をアラビア数字に統一したい場合、残念ながら一発で変換できる関数は用意されていません。手間はかかりますが、色々と関数を駆使すれば変換可能です。
最初から住所が分割されている場合は、住所1、住所2にそれぞれ割り当ててください。
達成目標
これを
こういった状態まで持っていきます。
20文字まで変換できます。同様の手順で最大文字数を増やすことも可能です。
20文字まで変換できます。同様の手順で最大文字数を増やすことも可能です。
漢数字→アラビア数字に変換
住所を分割
まずは大元の住所を分割します。
B1セルに=LEFT(A1,10)と入力し、Enterを押すと住所の左から10文字が表示されます。
B1セルに=LEFT(A1,10)と入力し、Enterを押すと住所の左から10文字が表示されます。
次にD1セルに=MID(A1,11,10)と入力し、再度Enterを押します。住所が二分割されました。便宜上、分割した左を住所1、右を住所2とします。
作った分割住所をオートフィルなどで縦に伸ばします。
列の挿入
住所1と2の間に5列挿入し、5列選択した状態で選択範囲を右クリック→列の幅をクリックします。
列の幅を1にします。
列の複製
狭くなった5列をそのままコピーし、
住所2の列頭（H）をクリックして、コピーしたセルの挿入をクリックします。
倍になった狭い10列を選択し、住所2の右の列頭で右クリック→コピーしたセルの挿入をクリックします。
住所1、2の右にそれぞれ10列できればOKです。
変換表の作成
どこか邪魔にならない場所に、漢数字とアラビア数字をセットで表にしておきます。漢数字を左にするようにしてください。その他変換したい文字があれば同様に記述します。
表をすべて選択し、
表をすべて選択し、
表左上の名前ボックスに変換表と入力して、Enterを二回押します。
関数の入力（1）
住所1の右1列目（C1）に、=IFERROR(VLOOKUP((LEFT(B1,1)),変換表,2,FALSE),LEFT(B1,1))と入力し、Enterを押します。
2列目（D1）に、=IFERROR(VLOOKUP(MID($B1,COLUMN()-2,1),変換表,2,FALSE),MID($B1,COLUMN()-2,1))と入力し、Enterを押します。
D1をオートフィルで右端いっぱいコピーします。
C1～L1を選択し、下に伸ばします。この時点で漢数字がアラビア数字に変換されます。
関数の入力（2）
住所1の右二列分の関数を、住所2右にコピーします。
Midの右にあるセル番号を二か所、住所2のあるセル（$M1）に修正します。
Columnの右にある数値を-11します。この場合COLUMN()-13となっていればOKです。
式をオートフィルで狭いセル右端いっぱい延ばします。
分割した住所の統合
一番右の列（X）に、=CONCAT(C1:L1)&CONCAT(N1:W1)と入力し、Enterを押します。
セルをオートフィルで下に伸ばします。
不要列の非表示（完成）
分割に使った列をすべて選択し、右クリック→非表示をクリックします。
すっきりした表になりました。これで完成です。
不要列の再表示
隠した列を含む範囲列を選択し、
ホームタブ→書式→列の再表示をクリックします。
非表示にしていた列が再表示されました。
関数部分の解説
Left、Mid
Left関数は、指定したセルの左から任意の文字数を抜き出すことができる関数です。
Left(A1,5）とすれば、A1セルの左から5文字だけを抽出できます。
Left(A1,5）とすれば、A1セルの左から5文字だけを抽出できます。
Mid関数では、指定セルの〇文字目からスタートして、任意の文字数を抜き出せます。
=Mid(B1,5,3)とすれば、B1セルの5文字目から数えて3文字を抜き出せます。
=Mid(B1,5,3)とすれば、B1セルの5文字目から数えて3文字を抜き出せます。
Column
Column関数は指定セルの列が左から数えて何列目かを返す関数です。
=column()とすれば書いたセル自身の列番号を返します。A1セルなら1、B1セルなら2となります。
=column()とすれば書いたセル自身の列番号を返します。A1セルなら1、B1セルなら2となります。
ここでは通し番号を作るのに使っています。
Mid関数で〇文字目からスタートを指定する時に、普通に数字で指定すると、オートフィルで延ばしても数字が増えない＝全部2文字目を抽出、ということになります。
Mid関数で〇文字目からスタートを指定する時に、普通に数字で指定すると、オートフィルで延ばしても数字が増えない＝全部2文字目を抽出、ということになります。
Column関数を使えば記述したセルの列数を返すので、セルが右にひとつずれれば数字が1増えます。
$
Mid関数の指定セルは、逆に同じ住所1ないしは2を選択し続けないといけません。
何もしないで右にオートフィルするとB1がC1、D1……とズレてしまうので、これを防ぐために$B1と記述することで、B部分を固定しています。
VLookUp
表を参照し、対応する値を見付けたら関連項目を表示する関数です。
ド定番関数ですがちょっと説明が大変なので、興味ある方はリンク先をご参照ください。
ド定番関数ですがちょっと説明が大変なので、興味ある方はリンク先をご参照ください。
IfError
VLookUpで参照している変換表には、漢数字以外の項目がありません。なので「A」などの例外文字を検索すると、何も見つからないのでエラーを返してしまいます。
IfError関数を使うと、「エラーが起きた時はエラー表示の代わりにこの文字を表示」といったことができます。
=IfError(値,エラーの場合の値)という構文で記述でき、ここではエラーの場合は抽出している文字をそのまま表示するようにしています。
=IfError(値,エラーの場合の値)という構文で記述でき、ここではエラーの場合は抽出している文字をそのまま表示するようにしています。
Mid関数のほうも同様の処理をしています。
Excel（エクセル）：住所、郵便番号
Excelをデータベースとして使う場合に使用頻度の高い、住所に関する操作、設定、便利技まとめです。
住所・郵便番号の入力
郵便番号から住所、住所から郵便番号を入力する方法です。
7桁の数字を入力すると、自動的に...
sunagitsune.com
2021.05.06
やりたいことから方法を探すエクセル（Excel）操作・関数・VBA（マクロ）逆引きまとめ
逆引き（やりたいことから探す）Excel記事まとめ
sunagitsune.com
2018.11.04
コメント