使用Excel进行地址，字符串操作 [英] Using Excel for address, string manipulation

问题描述

我目前正在处理城市地址上的大型数据源，这些数据看起来像这样.

I'm currently doing some work with a very large data source on city addresses where the data looks something like this.

来源:

否.史密斯街174号174号5楼

我使用了一个函数，该函数删除了extendoffice上显示的重复项. https://www.extendoffice.com/documents/excel/2133-excel-remove-duplicate-characters-in-string.html

i've used a function which removes the duplicates shown on extendoffice. https://www.extendoffice.com/documents/excel/2133-excel-remove-duplicate-characters-in-string.html

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
Dim x

With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each x In Split(txt, delim)
        If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
    Next
    If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
End With

结束功能

第二列变为:

否.史密斯街5号楼174号

我现在要做的是将其变成这个(删除编号并移动街道编号):

what I want to do now is to turn it into this (removing the NO. and moving the street number):

史密斯街174号5楼

实现这一目标的最佳方法是吗?有没有一种方法可以将街道号码移到街道名称旁边.

Would the the best way to accomplish this? Is there a way just to move the street number beside the street name.

我的想法是将地址分解为不同的字符串，然后将其放入不同的列中并重新排列.

An idea I had was to break down the address into different strings and putting them into different columns and rearranging them.

我也希望我可以在VBA中写一些东西，因为地址都是不同的长度并且有不同的间距

Also I was hoping I could write something in VBA, since the addresses are all different lengths and have various spacing

谢谢.

推荐答案

您可以尝试以下方法:

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
    Dim x As Variant, arr As Variant, temp As Variant

    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .count > 0 Then
            arr = Split(Replace(Join(.keys, delim), "NO." & delim, ""), delim)
            temp = arr(0)
            arr(0) = arr(1)
            arr(1) = temp
            RemoveDupes2 = Join(arr, delim)
        End If
    End With
End Function

这篇关于使用Excel进行地址，字符串操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！