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ベストアンサー
- 回答日時:
神奈川県 横浜市 **区 *** 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は大丈夫でした!!もし、おわかりでしたら教えていただけると助かります。宜しくお願いします。
補足日時:2006/09/16 00:07No.8
- 回答日時:
#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
- 回答日時:
このテの処理には「正規表現」が必須アイテムです。
既存のVBAでも、ある程度正規表現が使えるようになってはいますが、以下のDLLを採用すると、より細かな正規表現が使用できるようになります。
http://www.hi-ho.ne.jp/babaq/bregexp.html
また、住所データの間違いを訂正したり、区切り位置を検出し易くするため、その住所データ内容の形式を統一するよう整形しておく必要があります。
とくに、住所の入力がクライアント側の担当者が行っており、入力担当者が複数いる場合などは、通常使用しない文字が含まれているケースがありますので、整形作業は重要です。
例えば「1-2-3」などの「-」の部分が「ー(長音記号)」であったり、文字コード表から入力したらしき横罫線などが入っている場合もあります。
また、入りきらない場合「306号室」などの「号室」などを削除するなどの工夫も必要です。
しかし、整形は、いちいち手作業で行うのではなく、StrConv関数や正規表現を組み合わせれば可能です。
ご回答ありがとうございます!正規表現が必須なのですね。確かにデータ数がかなり多い中で半角、全角等が統一されていなかったりで困ったなと思っていました。VBAは勉強した事がないのでわからず、StrConv関数や正規表現は初めて知りました。これを機に試してみます。ありがとうございました!
No.6
- 回答日時:
追伸:
一応、出来ないと書きましたが、以下では、第二区切りパターンを替えれば、区切りが出来ます。
Const MPAT2 As String = "\S([ \sA-z]+)\S" '第二区切りパターン
正規表現は、その括弧( ) の中に入るものを、次の先頭に来るものとして、区切るようにします。
一応、D-301号室は、それで区切れます。Windows の RegExp を使用していますから、多少、機能的には弱いものがあります。
No.5
- 回答日時:
こんにちは。
私の書いたものには、あまりキチンと返事はされなかったようですが、一応、気になっていたものなので、もう少しレスさせていただきます。今、マクロでの処理を作ってみました。
しかし、いくつかの点で問題点が発生してしまいます。
****************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
- 回答日時:
失礼ながら、質問者さんのお求めになっている内容は、充分お金をとれるノウハウを含んでいます。
過去にラベル印刷用の「住所分かち書き」を含んだソフトを作成しン十万円頂戴したことがあります。
No.2
- 回答日時:
こんにちは。
私は、一応、今のところは、参考意見までにさせていただきます。他の方の解答で出来れば、それに越したことはありません。私は、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
- 回答日時:
関数だけでやるなら例えば
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というような数字が出るので、それで繋げる。
ダミー列を多数つくる必要があるのでシートを分けて作成すれば、最終シートにはきれいに並ぶかと思います。
ご回答ありがとうございます!!細かく区切らない方法ばかり考えていたので、全然思いつかなかったです。貴重なお時間いただき、ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) LEFT関数で文字数を指定しないで取りだす方法 7 2023/06/30 09:49
- Excel(エクセル) エクセルのマクロを教えてください。 1 2022/03/30 09:29
- Visual Basic(VBA) 特定の文字を簡単な操作で半角スペースに変換するか削除したい 2 2022/11/01 10:35
- Java Java 南京錠 2 2023/02/04 11:46
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Excel(エクセル) Excelの数字(文字列)合計について あるデータをダウンロードすると、数字データが全て文字列になっ 4 2022/09/26 21:21
- その他(プログラミング・Web制作) python質問 1 2023/08/14 11:54
- Excel(エクセル) Excelの文字列を数字に変換する方法について 6 2023/07/31 21:18
- Excel(エクセル) capeofdragonと申します Excel2016を使っておりまして 半角又は全角の任意文字列が 2 2022/10/31 13:51
- その他(プログラミング・Web制作) プログラミング pythonの問題について 2 2022/04/19 00:41
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
何故、日本は未だに数字を3桁...
-
CSVファイルの中で、「 , 」カ...
-
ひとつの命令を複数行に記述
-
マクロを使ってフォルダー内に...
-
テキストファイル内の「誤改行...
-
delphiで数値をカンマ区切りの...
-
Excel 住所の分割(文字数制限...
-
VBAでtxtファイルを読み込む際...
-
データにカンマが入ったCSVデー...
-
3桁ごと?4桁ごと?コンマの...
-
何故、日本は未だに数字を3桁...
-
JPY ¥1,500.00は日本円でお幾ら?
-
エクセルで数値を全角文字(カ...
-
カンマ区切りの数字をCSVフ...
-
VBScript 日付の比較について
-
英数字のみ全角から半角に変換
-
全角英数字の必要性が理解できない
-
パイソンエラーについて
-
Accessで日付や数値を全角で表...
-
Perlでのメール本文の解析について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
CSVファイルの中で、「 , 」カ...
-
エクセルで数値を全角文字(カ...
-
EXCELからCSVにすると余計なカ...
-
マクロを使ってフォルダー内に...
-
カンマ区切りの数字をCSVフ...
-
データにカンマが入ったCSVデー...
-
カンマ区切り
-
WORDで改ページすると時々グレ...
-
CSVの定義
-
C#で、テキストボックスの入力...
-
3桁ごと?4桁ごと?コンマの...
-
[VBA][Excel]クリップボードか...
-
何故、日本は未だに数字を3桁...
-
PHP カンマをエスケープしたい...
-
ひとつの命令を複数行に記述
-
「カンマ」と「コンマ」は同じ...
-
VB2005のTextBoxでカン...
-
JPY ¥1,500.00は日本円でお幾ら?
-
パス区切りの文字について
-
メッセージボックスの数字をカ...
おすすめ情報