Breaking up a string
 

I have an "Address" field in Excel that houses Street address, City, State, &
Zip, separated only by spaces. How do I go about separating strings into New
fields for City, State, and Zip? Is there some kind of VBA code I can use to
separate the larger string?

Breaking up a string
 

One way:

Select the data and then use DataText To Columns. Check Delimited and
Space as the delimiter. You may however run into problems where you have
spaces in the city name eg "New York"

Hope this helps
Rowan

jayceejay wrote:
I have an "Address" field in Excel that houses Street address, City, State, &
Zip, separated only by spaces. How do I go about separating strings into New
fields for City, State, and Zip? Is there some kind of VBA code I can use to
separate the larger string?

Breaking up a string
 

try this, assuming the string is in column A, and there is a name, address,
city, state, zip and phone.

if there are less, it should still work for you

Sub Macro1()
'
'
'
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'
Application.ScreenUpdating = False
For xR = LastRow To 1 Step -1

Cells.Replace What:=" ", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder
_
' :=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Next xR
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo
_
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
Columns("A:F").EntireColumn.AutoFit
Application.ScreenUpdating = True

End Sub
--


Gary


"jayceejay" wrote in message
...
I have an "Address" field in Excel that houses Street address, City, State,
&
Zip, separated only by spaces. How do I go about separating strings into
New
fields for City, State, and Zip? Is there some kind of VBA code I can use
to
separate the larger string?

Breaking up a string
 

If he has commas as he shows, he can use that as the delimiter.

--
HTH

Bob Phillips

"Rowan" wrote in message
...
One way:

Select the data and then use DataText To Columns. Check Delimited and
Space as the delimiter. You may however run into problems where you have
spaces in the city name eg "New York"

Hope this helps
Rowan

jayceejay wrote:
I have an "Address" field in Excel that houses Street address, City,
State, &
Zip, separated only by spaces. How do I go about separating strings
into New
fields for City, State, and Zip? Is there some kind of VBA code I can
use to
separate the larger string?