Split string with inconsistent delimiter
I have a column that contains city, state and zip code.
CITY_STA开发者_JAVA百科TE_ZIP
--------------------------------------------------
Monroe, IN 46711
South Bend, IN 46615
Alexandria, IN 46001
I wants the three words 'CITY_STATE_ZIP' to be split into different columns.
CITY STATE ZIP
---------- ---------- --------------------
Monroe IN 46711
South Bend IN 46615
Alexandria IN 46001
You can do it as follows, if you want to use VBA.
Sub splitIntoCols()
Dim oRange As Excel.Range
Dim oCell As Excel.Range
Dim vValue As Variant
Dim sCity As String
Dim sState As String
Dim sZipCode As String
Set oRange = ActiveWorkbook.Sheets(1).Range("A3:A100")
For Each oCell In oRange
'Takes the whole value
vValue = oCell.Value
'Retrieve the City name (with or without spaces)
sCity = Left(vValue, InStr(vValue, ",") - 1)
'Remove the city name from the array
vValue = Trim(Mid(vValue, InStr(vValue, ",") + 1))
'Split the value by spaces
vValue = split(vValue, " ")
sState = vValue(0)
sZipCode = vValue(1)
Next
End Sub
There is a menu item "Text to Columns" that launches a wizard to do this. (If you need VBA code, you can turn on the macro recorder and see what it generates.)
Supposing your cells are in column A:
Sub a()
Dim r As Range
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each k In r
Cells(k.Row, 4) = Mid(k, Len(k) - 5)
Cells(k.Row, 3) = Mid(k, Len(k) - 7, 2)
Cells(k.Row, 2) = Mid(k, 1, Len(k) - 10)
Next k
End Sub
I'm a litte unsure if you're trying to do this in a macro or spreadsheet. The following will work in a spreadsheet and can be modified for a macro.
Assuming your column of data in column D, and this example is spliting a string in D4.
Field D5 has the function
=LEFT(D4, FIND(",",D4)-1)
Field D6 has the function
=LEFT(MID(D4,FIND(",",D4)+2,LEN(D4)),FIND(" ", MID(D4,FIND(",",D4)+2,LEN(D4))))
Field D7 has
=RIGHT(MID(D4,FIND(",",D4)+2,LEN(D4)),LEN(MID(D4,FIND(",",D4)+2,LEN(D4))) -FIND(" ", MID(D4,FIND(",",D4)+2,LEN(D4))))
精彩评论