SPLIT function in VBA code query

[JayGatsby]

Hi guys,

Could you please help me with a code which would split Address string as per the pic attached?

- there are two types of inputs:

1) the one which starts with "~" after ";" and is structured - i.e. has 3 strings (first two separated by "," + "~" country) and
2) the one which contains street starts right after the separator ";" - which is the free text input. This one is a free text and I want it placed as a whole in a "street column" with only country split in "country location column". I do not care about the zip so this can be ignored.

Please note all the strings end with with country (separated by "~") input which is mandatory and needs to be populated to "location country" column.

Thanks a million!

Jay
Also posted VBA code for location string split [SOLVED]
and Split of the address string into separate lines as per defined structure - OzGrid Free Excel/VBA Help Forum

 

[sericom]

Give this a try, assumes source data is in columns A and B

Sub splitText()
    Dim startRow As Integer
    Dim ws As Worksheet
    Dim sourceRng As Range
    Dim strSplit() As String
    Dim str2Split() As String
   
    '------------------------------------------------------
    Set ws = ThisWorkbook.Sheets("Data")    'Worksheet name
    Set sourceRng = ws.Range("B5:B9")    'Source data range
    startRow = 14     'First row the results will appear in
    '------------------------------------------------------
    With ws
        For Each c In sourceRng.Cells
            strSplit = Split(c.Text, ";")
            .Cells(startRow, 1) = c.Offset(, -1).Value
             For Each itm In strSplit
                str2Split = Split(itm, "~")
                    .Cells(startRow, 2) = Trim(str2Split(2))
                    .Cells(startRow, 3) = Trim(Split(str2Split(1), ",")(1))
                    .Cells(startRow, 4) = Trim(Split(str2Split(1), ",")(0))
                    .Cells(startRow, 5) = Trim(str2Split(0))
                    startRow = startRow + 1
             Next
        Next
    End With
End Sub