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

質問

エクセルに関して教えてください。
顧客データをエクセルで管理しているのですが、CSVファイルで他のシステムに
移行することになりました。で、下記のようにひとつのセルになっている住所データを
二つのセルに分けるにはどうすれば良いのでしょうか？

新宿西新宿3-5-14
台東区上野6-22-3
さいたま市浦和5-8-9

というデータを；

新宿西新宿　　　　　　3-5-14
台東区上野　　　　　　6-22-3
さいたま市浦和　　　　5-8-9

のように、地名と番地の別のセルに分けたいのですが、、、。
やはり、マクロとかVBAでないとだめでしょうか？
（全然、詳しくないのでわかりやすく説明して頂ける
と助かります）

どうぞ、宜しくお願い致します。

回答No.2

MackyNo1 · ベストアンサー

元のデータがA1セルにあるなら、B1セルに以下の数式を入力すれば数字までの住所を表示できます。

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1)

番地部分は以下の数式になります。

=SUBSTITUTE(A1,B1,"")

お礼

おぉ、、、すごいです。
簡単にできました。感動！！

お忙しい中、ありがとうございました。
とっても助かりました！！

回答No.7

layy

参考レベルで

区別させる文字は、例にあるように、半角数字しかない、でしょうか。全角や漢数字だったりあるとさらに検討要でしょう。

地名だけ欲しいなら、半角数字と-をすべて削除して済むならそれでも可能かも知れない。後で番地を求める。

いずれにしても元のデータはシステム移行確認できるまで保管です。

お礼

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

がんばります！！

回答No.6

某HN クロメート(Chromate)（@CoalTar）

＃２のMackyNo1さんの方法で良いと思います

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

お礼

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

回答No.5

tom04

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

お礼

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

回答No.4

tom04

こんばんは！
一例です。
無理矢理って感じの方法になります。
↓の画像のように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

お礼

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

回答No.3

imogasi

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

お礼

本当に「おまじない」でした！!
有難うございます。
すごいですね。

これでなんとか締め切りに間に合いそうです。
助かりました！！

回答No.1

noname#204879

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

お礼

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

ありがとうございます。