Excel 住所の分割（文字数制限あり）

質問

Excelのシート1列に住所データがあり、これを30文字以内で収まらない場合は、次の列に20文字以内で、それでも収まらない場合はその次の列にデータを分割したいのですが、どうしたら良いでしょうか？
都道府県、市町村などの単位でスペースが入っていますが、最初の列は30文字以内なので単位としては細かすぎます。
またLEFT関数、MID関数を使用すると文字数は指定どおりになりますが、番地の途中や濁点などで列が変わったりしてしまいます。
適切な区切りもありつつ、文字数も守れるような方法はないでしょうか？
＜例＞
神奈川県　横浜市　**区　***　1-11-11　****************D-301号室（48文字、スペースも1文字にひとまず数えました）
　　　　　　これを下記のように変換したい
　　　　　　　　　　　↓
神奈川県　横浜市　**区　***　1-11-11（30文字以内、このデータは24文字）
****マンション名等*****　（1列目30文字に入りきらなかったので2列目20文字以内、このデータは16文字）
D-301号室　（2列目20文字以内にも入らなかったので3列目）
以上、よろしくお願いします。

回答No.3

mshr1962 · ベストアンサー

神奈川県　横浜市　**区　***　1-11-11　****************　D-301号室
のようにD-301号室の前にスペースがあるなら
A1にデータがあり、A2,A3,A4に分割として
A2=IF(LEN(A1)>30,LEFT(A1,MIN(FIND("　",A1&"　",MAX(LEN(A1)/2,20))-1,30)),A1)
A3=IF(LEN(A1)>30,MID(A1,LEN(A2)+2,MIN(FIND("　",A1&"　",LEN(A2)+2)-LEN(A2)-1),20),"")
A4=MID(A1,LEN(A2&A3)+2,20)
※D301号室の前にスペースがない場合、A4は"1号室"となります。

補足

ご回答ありがとうございます！！試してみたのですが、A2の式を入れると引数が多すぎるか、少なすぎるというメッセージが出てきてしまいます。いろいろ試してみたのですが、よくわからなくて。。A2とA4は大丈夫でした！！もし、おわかりでしたら教えていただけると助かります。宜しくお願いします。

回答No.8

mshr1962

#3です。すみません。転記時に)の位置を間違えたようです。
正：A3=IF(LEN(A1)>30,MID(A1,LEN(A2)+2,MIN(FIND("　",A1&"　",LEN(A2)+2)-LEN(A2)-1,20)),"")
誤：A3=IF(LEN(A1)>30,MID(A1,LEN(A2)+2,MIN(FIND("　",A1&"　",LEN(A2)+2)-LEN(A2)-1),20),"")

お礼

再度ご回答ありがとうございます！試してみたら出来ました！！マンション名の前の所は他の方にご回答いただいた方法でスペースを入れて処理できるかと思います。大変助かりました！！ありがとうございました！！！

回答No.7

noname#79209

このテの処理には「正規表現」が必須アイテムです。
既存のVBAでも、ある程度正規表現が使えるようになってはいますが、以下のDLLを採用すると、より細かな正規表現が使用できるようになります。
http://www.hi-ho.ne.jp/babaq/bregexp.html

また、住所データの間違いを訂正したり、区切り位置を検出し易くするため、その住所データ内容の形式を統一するよう整形しておく必要があります。

とくに、住所の入力がクライアント側の担当者が行っており、入力担当者が複数いる場合などは、通常使用しない文字が含まれているケースがありますので、整形作業は重要です。
例えば「１－２－３」などの「－」の部分が「ー（長音記号）」であったり、文字コード表から入力したらしき横罫線などが入っている場合もあります。

また、入りきらない場合「306号室」などの「号室」などを削除するなどの工夫も必要です。

しかし、整形は、いちいち手作業で行うのではなく、StrConv関数や正規表現を組み合わせれば可能です。

お礼

ご回答ありがとうございます！正規表現が必須なのですね。確かにデータ数がかなり多い中で半角、全角等が統一されていなかったりで困ったなと思っていました。VBAは勉強した事がないのでわからず、StrConv関数や正規表現は初めて知りました。これを機に試してみます。ありがとうございました！

回答No.6

Wendy02

追伸：

一応、出来ないと書きましたが、以下では、第二区切りパターンを替えれば、区切りが出来ます。

Const MPAT2 As String = "\S([　\sA-z]+)\S" '第二区切りパターン

正規表現は、その括弧( ) の中に入るものを、次の先頭に来るものとして、区切るようにします。
一応、D-301号室は、それで区切れます。Windows の RegExp を使用していますから、多少、機能的には弱いものがあります。

お礼

いろいろと考えてくださり補足のご回答いただき、ありがとうございます！！！

回答No.5

Wendy02

こんにちは。

私の書いたものには、あまりキチンと返事はされなかったようですが、一応、気になっていたものなので、もう少しレスさせていただきます。今、マクロでの処理を作ってみました。

しかし、いくつかの点で問題点が発生してしまいます。

****************D-301号室

>（1列目30文字に入りきらなかったので2列目20文字以内、このデータは16文字）
>D-301号室　（2列目20文字以内にも入らなかったので3列目）

こちらの住所録で調べてみましたが、例えば、「D-301」区切り相当する部分は、判断のしようがありませんね。スペースを入れていただくしかありません。以下の場合は、第二区切りパターン の工夫の仕方にあるように思いますが、分かりません。

たまたま、そこを区切れるように出来ても、統一のパターンで行うのは無理だと思います。（たぶん、お金を払ってでも、データ自体をいただかない限りは、パターンの作りようがありません。)

'標準モジュール

Sub MainMacro()
　Dim c As Range
　Dim i As Integer
　Dim j As Integer
　Dim k As Integer
　Dim n As Integer
　Dim buf As String, buf1 As String, buf2 As String
　Dim Ar() As Variant
　'B列/C列に出力されます。
　Const MPAT1 As String = "\S([　\s]+)\S"　'第一区切りパターン
　Const MPAT2 As String = "\S([　\s]+)\S"　'第二区切りパターン

　Application.ScreenUpdating = False
　For Each c In Range("A1", Range("A65536"))
　　If Len(c.Value) > 29 Then
　　　buf = c.Value
　　　buf1 = AddressSplit(c.Value, MPAT1, 29)
　　　c.Offset(, 1).Value = Trim(buf1)
　　　If Len(c.Offset(, 1).Value) > 19 Then
　　　　buf2 = AddressSplit(Replace(buf, buf1, ""), MPAT2, 19)
　　　　If Len(Trim(buf2)) > 0 Then
　　　　c.Offset(, 2 + n).Value = Trim(buf2)
　　　　n = n + 1
　　　　End If
　　　　c.Offset(, 2 + n).Value = Trim(Replace(buf, buf1 & buf2, ""))
　　　End If
　　End If
　　n = 0
　Next c
　Application.ScreenUpdating = True
End Sub

Function AddressSplit(strVal As String, myPat As String, SplitNum As Integer) As String
Dim Matches As Object
Dim Match As Object
Dim Ar() As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Erase Ar()
i = 0: j = 0
On Error GoTo ErrHandler
With CreateObject("VBScript.RegExp")
　.Pattern = myPat
　.Global = True
　Set Matches = .Execute(strVal)
　For Each Match In Matches
　 ReDim Preserve Ar(i)
　 Ar(i) = Match.FirstIndex + 1
　 i = i + 1
　Next

　If UBound(Ar()) < 0 Then Exit Function
　For i = LBound(Ar()) To UBound(Ar())
　　　If Ar(i) > SplitNum Then
　　　 j = i - 1
　　　 Exit For
　　　End If
　　　Next i
　　If j = 0 Then
　　　k = Ar(UBound(Ar()))
　　Else
　　　k = Ar(j)
　　End If
　AddressSplit = Mid$(strVal, 1, k)
End With
Exit Function
ErrHandler:
　AddressSplit = ""
End Function

お礼

マクロまで作っていただき、再度ご回答ありがとうございます！！マクロは全然わからないのですが、ご回答いただいたもので試してみたいと思います！きっと精度の高いデータができるのではと思います。ご丁寧にありがとうございました！！

回答No.4

noname#79209

失礼ながら、質問者さんのお求めになっている内容は、充分お金をとれるノウハウを含んでいます。
過去にラベル印刷用の「住所分かち書き」を含んだソフトを作成しン十万円頂戴したことがあります。

回答No.2

Wendy02

こんにちは。

私は、一応、今のところは、参考意見までにさせていただきます。他の方の解答で出来れば、それに越したことはありません。私は、VBAの処理にはなると思います。まず、関数では不可能だと思います。

<例> たぶん、実物から抜き出したものだと思います。

神奈川県　横浜市　**区　***　1-11-11　****************D-301号室

区切り選択位置
4
8
12
16
24
-(41)

***　1-11-11　　←ここの区切り位置(24)にはスペースがあるようですが、
*D-301号室←こちらにはありません。 -(41)

（Dの前に予想する文字列は、すべてだというなら、それは論理的には不可能だと思います。)

****************D-301号室
の「****************」は、たぶんカタカナだとは予想しますが。

せめて、区切り位置に、スペースがあるならともかく、そうでない場合は、正規表現でいけるかどうか、やってみなければ分かりません。

>番地の途中や濁点などで列が変わったりしてしまいます。
それに、番地は、「数字と[－]」の組み合わせだとして、それは良いとしても、「濁点」で変わるというのは、半角カタカナが存在する、という意味を示してはいないでしょうか？

どなたかのマクロの完成を待つにしても、よほどの多くを経験した者（つまり、パターンを持っている人)でないと、回答者側で様々なサンプルを使いながら行うので、当然、トライ&エラーを繰り返します。こちらでも、Word用のはがきの印刷のために、VBAで作った経験はありますが、できれば、手作業のほうが早いかもしれません。

お礼

貴重なお時間頂き、ご回答ありがとうございました！！

回答No.1

popesyu

関数だけでやるなら例えば
1.空白を単位にしてすべて列を分ける（テキストをCSV形式で読み直せば簡単に作成できます）。ある程度の規則がある住所なんで列は10ぐらいを上限として考えておけば十分でしょう。
2.列をすべて繋げた場合の文字数をカウントします。
3.それで30文字をオーバーするなら連結する列を一つ減らします（IF関数でネストする）
　ex.A1～A10までで30オーバーするならA1～A9までをカウント、の繰り返し
4.30文字の制限で何列目までが繋げられるのかをカウントする
　例えば1-4列目までで30文字以下に収まるなら4
5.カウントした値を元に同様に2行目に入る列数をカウントする
　今度はA5～A10までで検証。手順は3と同じ
6.最後にそれらを元にそれぞれ連結
　例えば1～4、5～6、7～10というような数字が出るので、それで繋げる。

ダミー列を多数つくる必要があるのでシートを分けて作成すれば、最終シートにはきれいに並ぶかと思います。

お礼

ご回答ありがとうございます！！細かく区切らない方法ばかり考えていたので、全然思いつかなかったです。貴重なお時間いただき、ありがとうございました。