人気ブログランキング | 話題のタグを見る

Excelワザ_セル内改行の削除→都道府県と市町村に分ける

住所が都道府県と市町村以下が
2行になっている住所録。
最終的に別セルに分けたいので,
まずセル内改行を取りたい。
Excelワザ_セル内改行の削除→都道府県と市町村に分ける_b0186959_12211496.jpg
1.置換を使います。
「clean関数」でもできるけど、簡単な方法で。
 Ctrl+H→置換ダイアログボックス
 「Ctrl+J」と入力。(何も表示されない(^-^;)
 が、無視して、OK
Excelワザ_セル内改行の削除→都道府県と市町村に分ける_b0186959_12392971.jpg
すると、
Excelワザ_セル内改行の削除→都道府県と市町村に分ける_b0186959_12395951.jpg
置換されて、住所が1行になりました。

次に、都道府県と市区町村以下を
それぞれD列とE列に分けて表示したい。
と、ここで、2013からついた新機能の
「フラッシュフィル」を使ってみた。
「東京都」と以下の住所を別々に入力し、
データ→フラッシュフィル
Excelワザ_セル内改行の削除→都道府県と市町村に分ける_b0186959_16564966.jpg
すると、
Excelワザ_セル内改行の削除→都道府県と市町村に分ける_b0186959_16581539.jpg

あ~~~、やっぱり規則性がないか(;^_^A
神奈川県、鹿児島県、和歌山県、この3県だけは、
4文字、それ以外は3文字
で取り出さないと、ダメね(^^;
やっぱり、関数か…(^^;

D列に「=IF(MID(C3,4,1)="県",LEFT(C3,4),LEFT(C3,3))」
もし、4文字目が「県」だったら、左から4文字分を取り出す、
そうでなければ、3文字分を取り出す。

E列に「=SUBSTITUTE(C3,D3,"")」
C列の文字列を、D列の文字を検索し、
空白に置き換える。
すると、都道府県以下の文字だけが表示される。

Excelワザ_セル内改行の削除→都道府県と市町村に分ける_b0186959_17182255.jpg

同じ数の都道府県なら、フラッシュフィルでできるけど、
全国のはダメね、フラッシュフィルの弱点でした(^^;





by school-pc | 2018-06-08 13:12 | Comments(1)

Commented by 有澤翔治 at 2021-09-15 18:39 x
フラッシュフィルの記述、興味深く拝見しました。
環境が揃っていないので検証はできませんが、他の記述とも考え合わせ、数式を使わずに分ける方法を考えました。
1.「東京都」を「東京都 」に置換
2.「大阪府」を「大阪府 」、「京都府」を「京都府 」にそれぞれ置換(「データに「府中」「大府」などがなければ「府 」で構いません)
3.「北海道」を「北海道 」に置換
4.「県」を「県 」に置換
そしてフラッシュフィルを使えばいかがですか?
(数式を組むのと、最大5回、検索ボックスに打ち込むのとどっちが手間かという話ですが……。Wordと違い、検索置換の記号が貧弱なのが難点ですね)