PDA

View Full Version : Separate text



av8tordude
05-07-2011, 05:38 AM
I have one column that consist of City, State, Country (separated by comma). Can someone assist with a code that will separate them into there own cell.
Thanks for your help.

Copy: Column A: Country, State, City

Paste into:

Column B: Country
Column C: State
Column D: City

Bob Phillips
05-07-2011, 06:06 AM
Just do Data>Text To Columns on that column

Kenneth Hobs
05-07-2011, 06:14 AM
Sub Test_SplitToRight()
Dim cell As Range
For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
If cell.Value2 <> Empty Then SplitToRight cell, ","
Next cell
End Sub

Sub SplitToRight(aRange As Range, delimit As String, Optional trimSpaces As Boolean = True)
Dim a() As String, i As Integer
If aRange.Count <> 1 Then Exit Sub
a() = Split(aRange.Value2, delimit)
If trimSpaces Then
For i = 1 To UBound(a)
a(i) = Trim(a(i))
Next i
End If
aRange.Offset(0, 1).Resize(1, UBound(a) + 1).Value2 = a()
End Sub

av8tordude
05-07-2011, 09:48 AM
Thank you both.