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

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.
--
Gary''s Student - gsnu200804


"TXDalessandros" wrote:

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

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

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

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

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

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

Pass it twice through DtaText 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

On Thu, 11 Sep 2008 08:46:04 -0700, TXDalessandros
wrote:

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