Split address in 1 cell into 4

T

TXDalessandros

I have a extract from the web that puts the address field all in one and I
need to split this by street address then city state and zip into the next 3
columns. My data looks like this when extracted. There is a Square for the
implied enter after the end of the street address

1107 Hendrick Dr Suite B
Carbondale, CO, 81623

Is there code that could be used to split this from say column F into F, G,
H and I?

Thank you
Holly
 
G

Gary''s Student

Assuming the little square is CHAR(10), we will first replace the little
square with a comma and then use Text To Columns. Pull-down;

Edit > Replace

1. click in the find what box
2. while holding down the ALT key, touch 010 on the numeric keypad
3. click in the replace with box and enter a comma

Now that we have removed all the squares and replaced them with commas,
click on the column and pull-down:

Data > Text to columns > Delimited and use the comma as the delimiter.
 
R

Rick Rothstein

This macro should do what you want (set the data start row and data column
in the indicated Const statements)...

Sub SeparateAddressParts()
Const DataStartRow As Long = 2
Const DataStartCol As String = "F"
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim CellValue As String
Dim Parts() As String
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, DataStartCol).End(xlUp).Row
For X = DataStartRow To LastRow
CellValue = .Cells(X, DataStartCol).Value
If Len(Trim(CellValue)) > 0 Then
CellValue = Replace(Replace(CellValue, vbCr, vbLf), vbLf & vbLf,
vbLf)
Parts = Split(CellValue, vbLf)
With .Cells(X, DataStartCol)
.Value = Parts(0)
Parts = Split(Parts(1), ",")
For Z = 0 To 2
.Offset(0, Z + 1) = Trim(Parts(Z))
Next
End With
End If
Next
End With
End Sub
 
G

Gord Dibben

Pass it twice through Dta>Text to Columns

Once with delimiter of CTRL + j to split into F & G

Then again on G with delimiter of comma to split into G, H & I

Record a macro whilst doing this.


Gord Dibben MS Excel MVP
 
T

TXDalessandros

I am getting a compile erro on this
CellValue = Replace(Replace(CellValue, vbCr, vbLf), vbLf & vbLf,
vbLf)
 
R

Rick Rothstein

Okay, that line should work fine in your version of Excel. I see in my
newsreader that the line of code, which is a single line of code, was broken
up into two lines. If your newsreader did that also, and if you simply
copied the code as posted, then you would get the error message you
indicated. The line in question is this one...

CellValue = Replace(Replace(CellValue, vbCr, vbLf), vbLf & vbLf,
vbLf)

The vbLf on the line all by itself should really be located at the end of
the longer line above it. If you do that, does the error message go away?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top