asked on

Split street address into multiple cells

I have a excel sheet containing 150 columns by 10500+ rows so i need this to be automated.
I have addresses of multiple companies which are placed in single columns. I'm trying to split these into multiple cells so i have a seperate street name, number and extra info (like the "a" in "11a").
There are more rows in the test file that i didn't include in the picture example. The column to be splitted is column "E".
I have included the test file and a picture of the before and after situations to help.
test.xls
problem.jpg

Dave

Hi,

Something like this regular expression should do it

It will dump the 3 portions into columns E to G (see attached example). Given you data length, I have used an array to speed up the process - it will be much quicker than a row by row loop

Cheers

Dave

Option Explicit
 
Sub splitAddress()
 
    Application.ScreenUpdating = False
    Dim myRange As Range, RegEx As Object
    Dim X, i As Long
 
    Set myRange = Intersect(ActiveSheet.UsedRange, Columns("E:E"))
    X = myRange.Resize(myRange.Rows.Count, 3)
    Set RegEx = CreateObject("vbscript.regexp")
 
    RegEx.Pattern = "([^\d]+)(\d+)(.+)*"
    For i = 1 To UBound(X)
        X(i, 2) = RegEx.Replace(X(i, 1), "$2")
        X(i, 3) = RegEx.Replace(X(i, 1), "$3")
        X(i, 1) = RegEx.Replace(X(i, 1), "$1")
    Next i
    myRange.Resize(myRange.Rows.Count, 3) = X
    Application.ScreenUpdating = True
End Sub

Open in new window

test.xls

thijs321

ASKER

is it possible to let the macro insert two columns where the data is placed. I have columns F and G filled with data so, if possible, it would be better to move F and G to the right by inserting two columns.

The rest of your macro works perfectly and fast so thanks for that

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thijs321

ASKER

Thanks man! very simple and clean solution. Saved me a half day's worth of cursing at excel and vbscript.

Dave

No probs, thx for the grade!

Cheers

Dave

geeksupport

hi,

How do you run this in excel?

Thanks in advance.

cheers
Ed