Separating string into columns using Excel VBA

Question

I'm trying to write a macro in VBA to split a bunch of addresses all in the same format into separate columns. So a street address column, suburb, postcode and state code column. The addresses all follow this format:

123 Fake Street, Suburbia QLD 4123

I wish I could approach this using SQL but I'm trying to keep this function inside an excel workbook where addresses would be central.

My planned approach is to write a for loop which counts the length of column D (where the addresses are stored)...so

   For LngRow = 2 To Wksht.Range("D" & Wksht.Rows.Count).End(xlUp).Row
       //concate/parse here//
   Next

and then it would follow a standard procedure of working backwards where it would separate and write the postcode (4 digits), then the state code (an array of state codes), then the suburb (the string between the state code and the delimiting comma after the street address), and finally the street address which is whatever string is remaining after the rest has been removed and rewritten.

I figure working backwards is best since the street address changes whereas the final 3 bits of info are standard.

Is it possible to write such a macro in VBA? Especially given how SQLish it seems.

cyboashu · Accepted Answer · 2016-08-17 08:49:08Z

1

going by description in the question and assuming that the address format will remain same, here is one approach using Split

Private Sub tt()
    Dim strTest     As String

    Dim arr1
    Dim arr2
    Dim arr3

    Dim StreetAddress As String
    Dim Postcode As String
    Dim StateCode As String
    Dim SubUrb As String


    strTest = "123 Fake Street, Suburbia QLD 4123"

    arr1 = Split(strTest, ",")
    StreetAddress = arr1(0)

    arr2 = Split(Trim(arr1(1)), Space(1))

    Postcode = arr2(2)
    StateCode = arr2(1)
    SubUrb = arr2(0)

End Sub

answered Aug 17, 2016 at 8:49

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

winghei Over a year ago

i think you need to take ubound of arr2 for postcode and state code just in case suburb is two words

cyboashu Over a year ago

if any part changes its size, whole code goes for a toss. :) It will give incorrect result. That's why the statment..going by description in the question and assuming that the address format will remain same.... some times one needs to hedge :) but thanks for the comment.

prizm1 · Accepted Answer · 2016-08-17 08:48:37Z

Excel already provides functionality which could achieve what you want. The only requirement is that you save the data to the disc and open it from a existing file. The function is called Workbooks.OpenText, but be aware that it exactly sticks to the given parameters, if you give in the wrong delimiter for example it may give you wrong results in the end or won't open the file correctly splitted.

Workbooks.OpenText "filename", _
DataType:=XlTextParsingType.xlDelimited, Origin:=XlPlatform.xlWindows, _
Space:=True, TextQualifier:=XlTextQualifier.xlTextQualifierNone

This would open a Space seperated Text or CSV file and put the values into many columns, returning an Workbooks Object were further operations can unfold.

The other approach would be to read the Text Linewise and use the VBA String Split to "manually" enter them in corresponding Rows and Columns, which has the advantage that you can manipulate data and strip out characters you don't want to have into the file on the fly. If you want I could give an example for that also.

Collectives™ on Stack Overflow

Separating string into columns using Excel VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related