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?

--
Rick (MVP - Excel)


"TXDalessandros" wrote in message
...
MS office std pkg 2003 sp

"Rick Rothstein" wrote:

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