VBAマクロでCSV取り込み　住所表記の変更について

前提

ヤマト運輸さんのB2プラットフォームに準じたCSVデータ読み込みをさせるマクロファイルを作っています。
特に支障はないのですが、届け先のマンション名など住所2に当たる箇所の修正を見栄えの為に修正を加えようと実装中に手詰まりしました。

参考になるサイトが有れば教えていただけますでしょうか。
お忙しいところ恐縮ですがご確認いただけますと幸いに存じます。

ヤマトB2の住所表記としてはマンション名含めて2行で出力します。
ヤマト配送ドライバーさんはバーコード管理しているので特に配送上の問題は有りませんが
たとえば、マンション名表記が長い場合や、濁点や半濁点、棟番号（第1、1棟など）などが2行目の先頭に表示され見栄えが悪くなるのでその場合にだけ、文字をセル移動（コピペ）の作業をします。

実現したいこと

ここに実現したいことを箇条書きで書いてください。

L列に（1行目）
都道府県名からの住所
について半角23文字を超えるとセルの色を変化させて、

LEN(INDIRECT(ADDRESS(ROW(),COLUMN())))>23

L列の字余りの文字数を区切りの良い場所で切り取って、隣のM列に貼り付けしています。
特に見栄えの悪いレコード行だけ修正したい訳です。

M列に住所の続き（2行目）

前提としてL列とM列は登録フォームに購入者が登録した内容が反映されます。
登録フォームは外部委託業者が契約に関わっており、契約しているのは行政の為
こちらは登録フォームそれ自体についての修正を加えることは出来ません。

発生している問題・エラーメッセージ

手作業でコピペをするのでレコード数が増えると手間。1回に200-300レコード程処理したい希望が有ります。

該当のソースコード

VBA
1
2模索中です。
3

試したこと

いろんなサイトを閲覧しましたが、うまく当てはめることが出来ませんでした。

参考になるサイトが有れば教えていただけますでしょうか。
お忙しいところ恐縮ですがご確認いただけますと幸いに存じます。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

CSV読み込みはクエリテーブルで取り込みしています。

住所のルールとしては、
➊マンション名記載がある（部屋番号を#101など　1-1-1-101のような書き方をしない）
場合、マンション名と番地情報の間に半角スペースが入っている場合が有る。全てのレコードに共通するルールではありません。

➋『バ』『パ』などの濁点のみ、半濁点のみ等が住所表記2行目先頭にくる場合『”』『　。』
【半角表記になります。書き方が分からなかったので雑に書きました。申し訳ございません。】

文字として1文字で成立する『バ』『パ』や区切りの良い箇所で2行目先頭になるように、M列に移動します。

❸『公営住宅1』『第1県営住宅』などの数字が住所表記2行目の先頭につく場合に、
番地や部屋番号と間違えないように、意味のある言葉や住宅名にそろえて、M列に移動させます。

❹マンション名は英字やカタカナ、数字から始まるものが多いです。

❺部屋番号1355　が『13』『55』とL列、M列に分かれることが有ります。

❻部屋番号には『号』『号室』がついてある表記とつかない表記とバラバラです。

❼番地に号とつける場合が有ります。

➑短い住所はマンション名含めてL列に収まることが稀に有ります。

❾・（ナカグロ）を使う場合がある。環境依存文字を使う方が稀にいるが、使用された場合にはメッセージボックスを表示するようにはしているので、イレギュラーな文字列については都度対応しますので、考慮外で構いません。

➓カタカナなど数字は基本的に半角の扱いです。

⓫1-1-1-1階 ABCマンション　や職場名を記入されるレコード行も偶にあります。

pig_vba

2022/12/14 05:37

区切りのいいところで切り取るとありますが、VBAの場合その区切りはどこで判断するのでしょうか？分割位置が常に23文字固定であれば楽に実装できそうですが変動させる場合結構面倒なことになりそうですが…

kikukiku

2022/12/14 05:40

上記コメントにあるように、「意味のある言葉や住所」を判断する部分が難しいと思います。どんな場合でも単に２行を１行に連結するだけで良いのではないでしょうか？周りに先輩たちがいると思いますので、そちらに相談した方が良いと思います。

pig_vba

2022/12/14 05:45

まぁ、Excelの場合隣のセルが空白なら自動的に枠をはみ出して表示してくれますし、単に１セルにstr1 & str2で出力してしまってもいいならそっちの方が楽かもしれませんね。実装自体の手間は固定長で分割と大して変わらないと思いますが

sh_azikikou

2022/12/14 05:50

特に困っている訳ではなく、ただの見栄えのみのお話です。入力者のさじ加減ですので、ある程度は考慮外にして、大部分のレコードについて処理できれば大丈夫です。2つのセルに入ってる文字列について、1つは固定22文字以内（ヤマトの出力上は1行目2行目）、もう1つマンション名は全角16文字まで（ヤマトの出力上は3行目の扱い）やっぱりルールが統一されず曖昧ですし難しいですね。ごめんなさい。

pig_vba

2022/12/14 06:00 編集

難しいと言っている部分は「どこで区切るかを自動で判別する(自然言語処理が必要)」部分なので固定で区切っていいなら難しくないですよー

行動規範の内容に同意します

回答2件

ベストアンサー

メインの処理はご自身でやってもらうとして、

Dim str1 As String  '一行目
Dim str2 As String  '二行目
Dim strSplit As String

Dim s As String
Dim row_ As Long  '操作したい行が入ってるとします(for文とかで回すと思います)


'処理~~~~

'判別部分
strSplit = ""
s = ""

If Len(str1) > 23 Then
    s = Mid(str1, 23, 1) '23文字目は濁音？
    If (s = Chr(222) Or s = Chr(223)) Then
        strSplit = Mid(str1, 22) '濁音だったので22文字目から切り取り
        Cells(row_, "L").Value = Left(str1, 21)
    Else
        strSplit = Mid(str1, 23)
        Cells(row_, "L").Value = Left(str1, 22)
    End If
End If

Cells(row_, "M").Value = strSplit & str2

こんな感じで実装できると思います。str1が22文字以下だった場合strSplitは空なので特に場合分けしなくても問題ないはずです

投稿2022/12/14 06:29

pig_vba

総合スコア807

sh_azikikou

2023/01/21 10:19

ご連絡ありがとうございます。 strSplit を毎回初期化するのを忘れないように気を付けます。良い感じに働きます。確認作業が遅くなり大変申し訳ございません。本当にありがとうございます。🐈

行動規範の内容に同意します

2月23日追記。

23文字目がアラビア数字の『0』のときセルの０落ちが見受けられたので、
取り急ぎ以下のように対応します。

若し使ってる方がいらっしゃいましたらごめんなさいｒｙ🐈
もっと良い方法があれば教えてください。。。

VBA
1Dim str1 As String  '一行目
2Dim str2 As String  '二行目
3Dim strSplit As String
4
5Dim s As String
6Dim row_ As Long  '操作したい行が入ってるとします(for文とかで回すと思います)
7
8'処理~~~~
9 'Columns("L:L").Select
10
11'判別部分
12strSplit = ""
13s = ""
14row_ = 1
15
16For row_ = 1 To Cells(Rows.count, 1).End(xlUp).Row
17
18str1 = Cells(row_, "L")
19str2 = Cells(row_, "M")
20
21If Len(str1) > 23 Then
22    s = Mid(str1, 23, 1) '23文字目は濁音？
23    If (s = Chr(222) Or s = Chr(223)) Then
24        strSplit = Mid(str1, 22) '濁音だったので22文字目から切り取り
25        Cells(row_, "L").Value = Left(str1, 21)
26    Else
27        strSplit = Mid(str1, 23)
28        Cells(row_, "L").Value = Left(str1, 22)
29    End If
30End If
31
32Cells(row_, "M").Value = "'" & strSplit & str2 '取り急ぎ0落ち回避処理　もっと良い方法が有れば教えて下さい
33
34strSplit = "" '初期化　しないとダメだった。。。
35       
36    Next row_
37