What version of Excel are you using?

--
Rick (MVP - Excel)


"TXDalessandros" wrote in message
...
I am getting a compile erro on this
CellValue = Replace(Replace(CellValue, vbCr, vbLf), vbLf & vbLf,
vbLf)

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"TXDalessandros" wrote in
message
...
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