Splitting Addresses

[privxls]

Hello again gurus!

Thank you for taking the time to check on this.

My current dilemma is that I have thousands of lines of addresses which I need to split into:
- Add L1
- Add L2
- City
- State
- Zip Code

But the format of the addresses is different per line (some have commas or separators) and my biggest challenge is that some addresses have Line 2 some don't have Line 2 (Apt # / Unit #).

Here is a sample list:

PO BOX 3000 legrande ave indianapolis in 46200

320 w 99th ave red springs nc 28355

2930 Martin Luther King Jr Way Unit B Berkeley CA, 947003

9995 Holly Ave, Silver Springs NV 89429

13500 W 888th Ave Apt 109 Thornton CO 80260

299 THATCHER LOOTS ELGIN SC 29405

55550 ROVEY AVE PHOENIX AZ 85019

58200 Driftwood Dr, Winter Haven, FL, 33884

7000 Chestnut St MIFFLINBURG PA 17844

Please note that the address list above may not be true/correct address as it is only solely for example purpose.

What could be the best way to split these addresses?

Here's a snapshot from the file:

I hope you guys can shed some light on this that I'm facing, thanks in advance!

 

[jasonb75]

Without a unique delimiter for every field of the address, you would only be able to split off the State (first 2 of last 8 characters) and Zip (last 5 characters) with any accuracy. But even that would get messed up by some having a comma delimiter and others not having it.

The rest of it would be impossible to split accurately as there is no way of knowing which of the spaces should be split and which should be left unsplit. Excel can not recognise the correct format in the same way that you can by looking at the list, it needs a set of rules to follow but I don't see enough consistency in your list to create any.

 

[privxls]

Without a unique delimiter for every field of the address, you would only be able to split off the State (first 2 of last 8 characters) and Zip (last 5 characters) with any accuracy. But even that would get messed up by some having a comma delimiter and others not having it.

The rest of it would be impossible to split accurately as there is no way of knowing which of the spaces should be split and which should be left unsplit. Excel can not recognise the correct format in the same way that you can by looking at the list, it needs a set of rules to follow but I don't see enough consistency in your list to create any.

That's what I thought, thanks for taking time to reply here Jasonb75. I guess my only resort now is to manually place a unique character to evenly split these addresses.

Appreciate your response!

 

[jasonb75]

Where was the list sourced from?

If it was copied from outside of excel then there is a (very slim) chance that there might be some line breaks in the addresses. I've never tried using them as delimiters before, but I guess it should be possible.

If there are line breaks, you would be able to see this by selecting column A then clicking 'Wrap Text' on the home tab of the excel ribbon. If it has line breaks then it will look how you want it too, but each address will be multiple lines in 1 cell rather than being split to 5 individual cells.

 

[mikerickson]

I would look at it starting from the right.
RIGHT(A1, 5) is zip
next comes the state,
Then the city (usually one word)
Then things get interesting.

In situations with inconstant data (different numbers of words per field), I've found these utility routines to be a big help.
Put this in the sheet's code module.
Double clicking on a cell will move the first word in that cell to be the last word of the cell to the left.

"Able Baker" "Charlie David" , when the user double clicks on "Charlie David" that will change to "Able Baker Charlie" "David"
if you right click on a cell, the last word of the cell will move to become the first word of the cell to the right.

Addresses always involve hand work. I hope this helps.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Rem move first word to cell on left
    Dim strMove As String

    With Target
        If 1 < .Column Then
            Cancel = True
            strMove = Split(Trim(.Value) & " ", " ")(0)
            .Value = Trim(Replace(.Value, strMove, vbNullString, 1, 1))
            .Offset(0, -1).Value = Trim(.Offset(0, -1).Value) & " " & strMove
        End If
    End With
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Rem move last word to cell on right
    Dim strMove As String
    Dim Words As Variant
    
    With Target
        If .Cells.Count = 1 Then
            Cancel = True
            Words = Split(Trim(.Text), " ")
            If 0 <= UBound(Words) Then
                strMove = Words(UBound(Words))
                Words(UBound(Words)) = vbNullString
                .Value = Join(Words)
                With .Offset(0, 1)
                    .Value = strMove & " " & Trim(.Text)
                End With
            End If
        End If
    End With
End Sub

 

[privxls]

I appreciate all your responses, thank you!

I was able to manually do it and it took some time, lol. I will be trying these suggestions you have and will post back in here to give you an update.