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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.