• ベストアンサー

エクセルに関して教えてください。

エクセルに関して教えてください。 顧客データをエクセルで管理しているのですが、CSVファイルで他のシステムに 移行することになりました。で、下記のようにひとつのセルになっている住所データを 二つのセルに分けるにはどうすれば良いのでしょうか? 新宿西新宿3-5-14 台東区上野6-22-3 さいたま市浦和5-8-9 というデータを; 新宿西新宿      3-5-14 台東区上野      6-22-3 さいたま市浦和    5-8-9 のように、地名と番地の別のセルに分けたいのですが、、、。 やはり、マクロとかVBAでないとだめでしょうか? (全然、詳しくないのでわかりやすく説明して頂ける と助かります) どうぞ、宜しくお願い致します。

質問者が選んだベストアンサー

  • ベストアンサー
  • MackyNo1
  • ベストアンサー率53% (1521/2850)
回答No.2

元のデータがA1セルにあるなら、B1セルに以下の数式を入力すれば数字までの住所を表示できます。 =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1) 番地部分は以下の数式になります。 =SUBSTITUTE(A1,B1,"")

halihali55
質問者

お礼

おぉ、、、すごいです。 簡単にできました。感動!! お忙しい中、ありがとうございました。 とっても助かりました!!

その他の回答 (6)

  • layy
  • ベストアンサー率23% (292/1222)
回答No.7

参考レベルで 区別させる文字は、例にあるように、半角数字しかない、でしょうか。全角や漢数字だったりあるとさらに検討要でしょう。 地名だけ欲しいなら、半角数字と-をすべて削除して済むならそれでも可能かも知れない。後で番地を求める。 いずれにしても元のデータはシステム移行確認できるまで保管です。

halihali55
質問者

お礼

ありがとうございます。 参考にさせて頂きます。 こういった事がスラスラできてたら、 本当にすごい仕事が進むんだと実感しています。 がんばります!!

回答No.6

#2のMackyNo1さんの方法で良いと思います 例示だけなら全角と半角を利用した方法で =LEFT(A1,LENB(A1)-LEN(A1)) =RIGHT(A1,LEN(A1)*2-LENB(A1)) も可能です。ただ、説明には沿っていないので参考まで。

halihali55
質問者

お礼

なるほど! 細かいところまで ありがとうございました!!

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.5

No.4です! たびたびごめんなさい。 前回の投稿で数式の方は大丈夫だと思うのですが、 VBAのコードを少し訂正してください。 「With・・・」の後の2行の順番を入れ替えておかないと数値の方がちゃんと表示されませんでした。 2回同じマクロを実行すればちゃんと表示されると思いますが、コードを訂正した方が良いと思うので もう一度コードを載せておきます。 尚、Excelの「余計なお世話・・・?」でハイフン付きの数値を日付データと扱う場合があり セルの左上角にエラーチェックのマークが付く場合がありますので、 当方使用のExcel2003の場合ですが、 メニュー → ツール → オプション → 「エラーチェックタブ」で「2桁年のテキスト日付」のチェックをはずしておけば エラーチェックのマークは付かないと思います。 コードは↓の通りです。 Sub test() Dim i, j As Long Dim str, buf As String For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row For j = 1 To Len(Cells(i, 1)) str = Mid(Cells(i, 1), j, 1) If str Like "[0-9]" Or str = "-" Then str = "" End If buf = buf & str Next j Cells(i, 2) = buf With Cells(i, 3) .NumberFormatLocal = "@" .Value = WorksheetFunction.Substitute(Cells(i, 1), Cells(i, 2), "") End With buf = "" Next i End Sub ちゃんと検証せずに投稿してごめんなさいね。m(__)m

halihali55
質問者

お礼

いえいえ、こちらこそ丁寧にご回答いただき、恐縮です。 本当にありがとうございました。

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.4

こんばんは! 一例です。 無理矢理って感じの方法になります。 ↓の画像のように2行目からデータがあるとします。 C列の数式から説明します。 C2セルに =IF(A2="","",MID(A2,MATCH(TRUE,ISNUMBER(MID(A2,ROW($A$1:$A$30),1)*1),0),10)) これは配列数式になってしまいますので、この画面からコピー&ペーストしただけではエラーになると思います。 貼り付け後、F2キーを押す、またはC2セルをダブルクリック、または数式バー内で一度クリックします。 編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定してください。 数式の前後に{ }マークが入り配列数式になります。 (A列の文字数が30文字まで対応・最初の数字出現から10文字を表示するようにしています。) B2セルは =IF(A2="","",SUBSTITUTE(A2,C2,"")) これは配列数式ではありません。 最後にB2・C2セルを範囲指定し、C2セルのフィルハンドルで下へコピーすると 画像のような感じになります。 VBAだと↓のコードを操作したいSheet見出し上で右クリック → コードの表示 を選択し、 白い画面に↓のコードをコピー&ペーストしてマクロを実行してみてください。 Sub test() Dim i, j As Long Dim str, buf As String For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row For j = 1 To Len(Cells(i, 1)) str = Mid(Cells(i, 1), j, 1) If str Like "[0-9]" Or str = "-" Then str = "" End If buf = buf & str Next j Cells(i, 2) = buf With Cells(i, 3) .Value = WorksheetFunction.Substitute(Cells(i, 1), Cells(i, 2), "") .NumberFormatLocal = "@" End With buf = "" Next i End Sub このコードも2行目からデータがあるとしています。 以上、長々と失礼しました。m(__)m

halihali55
質問者

お礼

詳しい説明、有難うございます。 皆さんの知識が素晴らしいですね。 本当に勉強になります!! ありがとうございました!!

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.3

文字列の中で最初に出てくる(半角)数字の位置(何文字目)を割り出すエクセル関数の方法に 定石(その筋で有名)があります(先人の知恵)。 例データ A列     空き列(E列とする) 新宿西新宿3-5-14 6 さいたま市浦和5-8-9 8 関数はE1セルに =MIN(FIND({"1","2","3","4","6","7","8","9"},A1&"1234567890"))と入れて SHIFT+CTRL+ENTERの3つのキーを同時押しする。 下方向に式を複写する。 これで左半分は =LEFT(A1,E1-1) で 新宿西新宿 右半分は =RIGHT(A1,LEN(A1)-E1+1) で 3-5-14 ーーー ”0”を入れてないのは01-2-1などは無いとしたから。 住所の丁目などが全角数字なら上の式の””の中を全角数字にしてください。 ーー 意味は1から9までの数字について、A1セルの文字列に対し、Find(a1,"1")など9回を行い、返ってくる数(位置)で最小(と言うことは一番最初)の数(位置)を出している。 Find関数はその数字文字が見つからないとエラーを返し、9回分全体としては、エラーになり混乱するので、強制的に"1234567890")を後部にくっつけて、元の文字列で9種の数字のどの数字が見つからない場合も、この部分で必ず見つかるようにするため。元の文字列より、後の方なので、数字文字の一番左の位置を求めるのに影響しない 数字がない場合は文字数+1の値が返る。 この対処は略。 ーーーーーーーーーーーーー 外にも理解の難しい関数もありますが略。 VBAででも使って先頭から文字が数字文字か聞いていくような方法が使えず、簡単には出来ない。 こんな難しい式になるが、おまじないと思って今回だけ使うしかないかと思う。

halihali55
質問者

お礼

本当に「おまじない」でした!! 有難うございます。 すごいですね。 これでなんとか締め切りに間に合いそうです。 助かりました!!

noname#204879
noname#204879
回答No.1

「セルになっている住所データを二つのセルに分ける」ことが、「CSVファイルで他のシステムに移行する」ことと、どう関係するのでしょうか?

halihali55
質問者

お礼

早々と回答いただき、ありがとうございます。 今のシステムでは住所は一つのセルですが、新しいシステムでは いくつか項目が別れており、現行の住所をいくつかのセルに別ける 必要がでてきたんです。 ありがとうございます。

関連するQ&A

  • MS-IME2000の地名辞書の更新について

    MS-IME2000の地名辞書について教えて下さい。 たとえば「さいたま市」ですが、元の浦和市の郵便番号を入力すると、「浦和市」と表示されてしまいます。最新の地名にするにはどうしたらよいのでしょうか。

  • エクセルで8-8という番地が日付になってしまいます

    郵便番号の更新データ(.csv)をダウンロードし エクセルで開いたいのですが8-8という番地のセルが 8月8日になってしまいました。 書式を文字型にしても日付→文字という変更で 38207と表示されてしまいます。元の8-8にする 方法はありますでしょうか?

  • csvをExcelで表示したとき、「-」を日付と認識させたくない

    お世話になります。 顧客情報のデータをcsvに吐き出した際、住所のデータが町名と番地に分かれて出力されています。すなわち、「address1」に「東京都渋谷区○○」、「address2」に「1-2-3」という感じです。 これをExcelで表示すると、「1-2-3」の部分が「2001/2/3」となって表示されています。日付と認識しているためですよね。 なのでこれをこのままセルの書式設定で「文字列」にすると、「36925」というシリアル値になってしまいます。 これを、Excelに取り込んだときにそもそも日付と認識させず、「-」を「-」のまま表示させるにはどうしたらいいでしょうか。 もし方法がありましたら教えていただきたいと思います。 よろしくお願いいたします。

  • 二個下の質問で「さいたま市の木のつく地名」をした者です

    みなさん、素早いお返事ありがとうございます 感動してます。 でも、どうも、ハッキリしなくて…(><) さいたま市で、番地が「3○○○」という風に、三千番台の番地がある、地名をご存知な方はいらっしゃいませんか? すみません、わかりづらい質問で…。

  • エクセルでのデータ集計方法について

    エクセルで、【1.元データ】のようなデータがシート毎に5,000社ほどあります。このデータを各市区町村ごとに集計を取りたいのです。 【1.元データ】 A社 ┃ 東京都品川区戸越 B社 ┃ 東京都新宿区下落合 C社 ┃ 東京都新宿区新宿 D社 ┃ 埼玉県川口市柳崎 E社 ┃ 神奈川県川崎市中原区丸子通 そこで、ピボットテーブルを使って集計したかったのですが、同じセルの中に番地までデータが入っていて【2.集計後のイメージ】のような意図している集計が出来なかったです。 【2.集計後のイメージ】 品川区 ┃ 1 新宿区 ┃ 2 川口市 ┃ 1 川崎市 ┃ 1 これを解決するにはどういう集計の仕方をすればいいでしょうか? 現在はエクセル上で住所データで並べ替えをして数えて集計をしているのですが、時間がかかってしまうため、何かいい方法はありませんか?

  • Excelの編集方法について

    標題、質問です。 Excel2013を利用中です。 セル番地指定で、文字列を入力したい事が目的です。 サンプルを添付します。 入力したいセル番地は"$C$6"と予めデータで存在。 入力したい文字列も、「桃太郎」のように。 それぞれ別のセルにデータとして存在するのですが、 ここからの処理についてご教示ください。 なお、当方の環境が、VBA利用NGとなっていますので、 関数構成でお助け下さい。 お手数ですが、どうぞ宜しくお願い致します。

  • エクセルVBAで困ってます。

    エクセルVBAで困っています。 データ入力済みのシートが2つあります。 シート名を「Sheet1」「Sheet2」とします。 「Sheet1」のA列のデータが「Sheet2」のA列のデータと一致した時に それぞれのシートのセル番地を取得したいのですが出来ません。 教えて下さい。 データの並び順は「Sheet1」と「Sheet2」で異なります。

  • JR宇都宮線と高崎線

    池袋(新宿)発/行きの宇都宮線/高崎線はたまにめちゃくちゃ遅いのがありますが、何であんなに遅いんでしょうか。 仮にも快速アーバンと名の付く池袋行きが、普通列車の上野行きにどんどん抜かれていく。 さいたま新都心にも浦和にも止まらないのに。 たまに速いのがあるけど。 だれか知りませんか。

  • さいたま市で、「木」のつく町名を教えてください。

    昔の友達の住所で、どうしても思い出せない地名があります。番地はなぜか覚えているのですが…(^^;) で、さいたま市の中で「木」のつく町名だったと思うのです。どう調べていいか分からないのです。 たぶん、字面を何個かみれば、「これだ!」と思い出すと思うのです。 よろしくお願いします。

  • エクセルのデータの半角スペースを利用し改行したい

    NotesのデータをCSVで書き出しエクセルで活用しようと思っておりますが、書き出したCSVには改行が反映されておりません。連続した文章になります。 ただ、改行された文書の頭には半角スペースがあります。 エクセルでデータ活用したいのですが、数千のテキストデータをチェックして改行していくのは非常に大変であり、途方にくれております。 例)    日曜日にスマホを購入しました。「改行」  その際、タブレットも勧められました。    ↓ 上記NotesのデータをCSVで書き出すと    日曜日にスマホを購入しました。 その際、タブレットも勧められました。  となります。 「その際」 の左に半角スペースが付けれております。 エクセルの一つのセルにNotesのデータと同様に改行の入ったテキストを表示したいと思っております。 CSVをエクセルにインポートしVBAを活用して隣のセルにでも再度書き出す方法がいいのかな?とも思ったりしますが、あまり知識がなく、どなたか助けていただけないでしょうか。 VBAの基礎は理解しているつもりではありますが、勉強不足で難しいことができません。 何卒、よろしくお願いいたします。  

専門家に質問してみよう