Split address in 1 cell into 4

[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

 

[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.

 

[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

 

[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

 

[TXDalessandros]

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

 

[Rick Rothstein]

What version of Excel are you using?

 

[TXDalessandros]

MS office std pkg 2003 sp

What version of Excel are you using?

 

[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?