How to split addresses into separate columns without commas/delimiters

[dobro1856]

Hi,

I'm hoping someone can help figure out how to separate a series of addresses in one column into separate columns, when there are no columns to use for Text to Columns.

I have a large sample file of addresses and was able to get 75% of them (the ones correctly formatted with commas) separated by Text to Columns, but the remaining ones all have different formats and I'm unable to figure out a solution. The sample file is huge, so I anonymized four records but otherwise left the format of the sheet exactly as in the original version. Does anyone have any ideas for a formula that I can just drag down for all the records in my original file?

Thank you so much for your help!

Sample File.xlsx
	A	B	C	D	E
1	ID	Source	First name	Last Name	Address
2	XY1	Sample File	Jane	Smith	PO BOX 111 CANTONMENT FL 32533
3	XY2	Sample File	Max	Mitchell	1111 N 9TH AVE APT 210 PENSACOLA FL 32503-2866
4	XY3	Sample File	John	David	111 FERNWOOD ST PANAMA CITY BEACH FL 32407-3052
5	XY4	Sample File	Oliver	Rosen	1111 CASTLE POINTE WAY PENSACOLA FL 32506-8357
Sheet1

 

[Rick Rothstein]

Give this macro a try...

Sub SplitAddressIntoColumns()
  Dim R As Long, Arr As Variant, SubArr As Variant
  Arr = Range("E2", Cells(Rows.Count, "E").End(xlUp))
  ReDim Preserve Arr(1 To UBound(Arr), 1 To 4)
  For R = 1 To UBound(Arr)
    Arr(R, 4) = Mid(Arr(R, 1), InStrRev(Arr(R, 1), " "))
    Arr(R, 3) = Mid(Arr(R, 1), InStrRev(Arr(R, 1), " ", InStrRev(Arr(R, 1), " ") - 1) + 1, 2)
    SubArr = Split(Left(Arr(R, 1), Len(Arr(R, 1)) - Len(Arr(R, 3)) - Len(Arr(R, 4)) - 1), "  ")  'That last set of quotes has 2 blanks between them
    Arr(R, 1) = SubArr(0)
    Arr(R, 2) = SubArr(1)
  Next
  Range("F2").Resize(UBound(Arr), 4) = Arr
End Sub

Note: As written, the output starts in Column F leaving your original data in place. If you wanted to overwrite the original data, change the F2 in the last line of code to E2.

 

[Joe4]

Rick,

I was trying your solution on the data provided, and it errored out for me on the very first record.
It returns "Run-time error 9, subscript out of range" on this line:

Arr(R, 2) = SubArr(1)

Did it work for you on the data provided?

 

[Rick Rothstein]

Yes, it worked for me (tested before posting). Note that there are 2 space between the street address and the city (comes into play in the Split function to create the SubArr array). How did you "copy" the data from the OP's post? I used the double page icon on the posted grid where the row headers intersect with the column headers.

 

[Joe4]

How did you "copy" the data from the OP's post?

I see. I just did the standard copy/paste, which looks like converts all the double-spaces to single-spaces.
I was wondering how you were able to do it with multiple word streets and cities. I did not notice the double-spaces.

I manually inserted them, and tried your code again, and all looks good!

 

[kweaver]

Rick. I don't suppose this can be solved if there's only 1 space before the city as there wouldn't be consistent structure to the column's addresses.

 

[Peter_SSs]

@Rick Rothstein
Suggestion

Arr(R, 4) = Mid(Arr(R, 1), InStrRev(Arr(R, 1), " "))
Arr(R, 4) = Mid(Arr(R, 1), InStrRev(Arr(R, 1), " ") + 1)

Does anyone have any ideas for a formula ...

Welcome to the MrExcel board!

You could try these.

21 02 24.xlsm
	E	F	G	H	I
1	Address
2	PO BOX 111 CANTONMENT FL 32533	PO BOX 111	CANTONMENT	FL	32533
3	1111 N 9TH AVE APT 210 PENSACOLA FL 32503-2866	1111 N 9TH AVE APT 210	PENSACOLA	FL	32503-2866
4	111 FERNWOOD ST PANAMA CITY BEACH FL 32407-3052	111 FERNWOOD ST	PANAMA CITY BEACH	FL	32407-3052
5	1111 CASTLE POINTE WAY PENSACOLA FL 32506-8357	1111 CASTLE POINTE WAY	PENSACOLA	FL	32506-8357
Split Address

Cell Formulas
Range	Formula
F2:F5	F2	=LEFT(E2,FIND(" ",E2)-1)
G2:G5	G2	=MID(E2,LEN(F2)+3,LEN(E2)-LEN(F2&H2&I2)-4)
H2:H5	H2	=LEFT(RIGHT(E2,LEN(I2)+3),2)
I2:I5	I2	=TRIM(RIGHT(SUBSTITUTE(E2," ",REPT(" ",20)),20))

 

[dobro1856]

Thank you all so much - I was able to get this resolved thanks to your help!

 

[Katich]

Thank you all so much - I was able to get this resolved thanks to your help!

Hello. What route did you go as i'm having the same issue.

 

[Peter_SSs]

Hello. What route did you go as i'm having the same issue.

I think that is answered by post #2 being marked as the solution.

.. but note that the OP's data has a double space before the city name, so perhaps you may not quite have "the same issue".