Split home address separated with a comma

Split home address separated with a comma

how to separate address in excel without comma
commas in addresses examples
separate parts of an address in excel
excel separate address numbers from street name
kutools split address
split address online
excel split street number
how to split address into multiple columns

I have about 15 Address that are formatted like, 1111 Really Cool Street, Sweet City, Awesome State I need to split them up into separate cells for their Street, City and State.

The problem I have currently is storing the Street,City and State. I've tried something along the lines..

Dim addressArray() As String

posRow = 1
Do
posRow = posRow + 1
    addressFromCell= Sheet1.Cells(posRow , "C")
    addressArray() = Split(addressFromCell, ",")
        Sheet2.Cells(posRow , "A") = addressArray(0)
        Sheet2.Cells(posRow , "B") = addressArray(1)
        Sheet2.Cells(posRow , "C") = addressArray(2)
Loop Until posRow = 15

I thought addressArray would look like {"1111 Really Cool Street","Sweet City", "Awesome State"} and I could simply pass in the element I wanted. Instead I keep on getting

Run-time error '9': Subscript out of range

Any help would be great, thanks!


As Ken White pointed out, the reason for the error is that one of the values doesn't contain 2 commas.

Here is how I would do it:

Dim addressArray() As String

posRow = 1
Do
    posRow = posRow + 1
    addressArray() = Split(Sheet1.Cells(posRow, "C").Value, ",")
    Sheet2.Cells(posRow, "A").Resize(1, UBound(addressArray) + 1).Value = addressArray()
Loop Until posRow = 15

This code uses the UBound function to retrieve the Upper Bound (number of the last element) for the Array and ensures that the Range is always the same size as the Array.

Split an address separated by comma, I have a data.frame of house sale transactions. At the moment the Address is in format. 1 Accacia Avenue,  If the addresses in your file are separated by commas such as '1 Union Street, San Francisco, CA 94111', you should select Comma as the Delimiter and then press Next. 5. This next page will show you what your list of addresses will look like. You can press Finish to see the result of your split out addresses.


To avoid any such error, you should check if the cell is not blank and the address in the cell is not the complete address.

posRow = 1
Do
posRow = posRow + 1
    addressFromCell = Sheet1.Cells(posRow, "C")
    If addressFromCell <> "" Then
        addressArray() = Split(addressFromCell, ",")
        If UBound(addressArray) = 2 Then
            Sheet2.Cells(posRow, "A") = addressArray(0)
            Sheet2.Cells(posRow, "B") = addressArray(1)
            Sheet2.Cells(posRow, "C") = addressArray(2)
        End If
    End If
Loop Until posRow = 15

How to Separate an Address That's In One Cell Separated by One , So, I need the address to be split into separate columns (depending on the amount of commas and address lines.)e.g.123 John Street  Choose Delimited from the first page, then on the second page clear all of the standard delimiters except Comma and click Finish in the lower right. Repeat on the newly split off state and zip using Space as the delimiter. Now insert a new blank column between the address/city and the state.


Try,

Sub test()
    Dim vArray
    Dim vDB, vR()
    Dim i As Long, r As Long, j As Integer
    Dim k As Integer
    Dim s, e
    's = Timer
    vDB = Range("c2", "c15")
    r = UBound(vDB, 1)
    ReDim vR(1 To r, 1 To 3)
    For i = 1 To r
        vArray = Split(vDB(i, 1), ",")
        k = UBound(vArray)
        If k > 0 And k < 3 Then
            For j = 1 To k
                vR(i, j) = vArray(j - 1)
            Next j
        End If
    Next i
    With Sheet2
        .Range("a2").Resize(r, 3) = vR
    End With
    'e = Timer
    'Debug.Print "Test() time : " & (e - s)
End Sub

Split home address separated with a comma, I have about 15 Address that are formatted like, 1111 Really Cool Street, Sweet City, Awesome State I need to split them up into separate cells for their Street, City  I have a spreadsheet in where the addresses are listed with the street then an on the next line the city, state code and zip separated by spaces. See example below 1234 Western Ave apt 8 Faribault MN 55021 I need to split the addresses into separate cells like so: 1234 Western Ave


How to Use Commas in Addresses and Dates, If you put Belle's address into a sentence, you have to separate each item of the the house number and street are not separated by a comma, nor are the state  Re: splitting address into separate columns when there's no delimiter. Hi johnmerlino, There is no repeat patern in your address to have Excel 2003 function to split as you wish. The main problem is different address have diffent "ST", "AVE", "CT" and etc.


Splitting a full address into three or more separate cells in Microsoft , A. Delimited:-Character such as commas or tabs separate each field. B. Fixed Width: - Fields are aligned in columns with spaces between each field. A delimiter is a sequence of one or more characters used to specify the boundary between separate, independent regions in plain text or other data streams. An example of a delimiter is the comma character, which acts as a field delimiter in a sequence of comma-separated values.


Punctuation: End Marks and Commas , A second comma is used after the year to separate it from the rest of the sentence​. Her address is 3425 Stone Street, Apt. 2A, Jacksonville, FL 39404. If using a  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more Split home address separated with a comma