Dave T
05-26-2013, 08:05 PM
Hello All,
I have a range of Longitude and Latitude values; all that are separated by commas but some may also have space after the comma.
I am after a macro that if a range of cells is selected (column may vary) it will copy the selected range and paste the first part of the string before the comma into the cell to the first right of the original value and the next part in the second cell to the right.
'-36.759327,141.847336 = -36.759327 | 141.847336 (Works fine)
'-36.759327, 141.847336 = Run-time error ‘5’:
The following macro works well but only works on a single cell (i.e. ActiveCell) at a time and if there is a space after the comma a Run-time error ‘5’: occurs.
Can the following macro be modified to cope with either no space after the comma or if there is a space.
Is there a better solution that someone could offer...
Sub zx()
'http://superuser.com/questions/352253/how-do-i-separate-a-comma-separated-list-into-two-columns-in-excel
Dim a() As String
Dim v As Variant
Dim i As Long
Dim j As Long
a = Split(ActiveCell.Value, " ")
ReDim v(1 To UBound(a) + 1, 1 To 2)
For i = 1 To UBound(a) + 1
j = InStr(a(i - 1), ",")
v(i, 1) = Val(Left(a(i - 1), j - 1))
v(i, 2) = Val(Mid(a(i - 1), j + 1))
Next
ActiveCell.Offset(0, 1).Resize(UBound(a) + 1, 2) = v
End Sub
I have recorded a macro that copes with or without spaces... but applies to range B8 only:
Sub Macro2()
'
Selection.TextToColumns Destination:=Range("B8"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub
Regards,
Dave T
I have a range of Longitude and Latitude values; all that are separated by commas but some may also have space after the comma.
I am after a macro that if a range of cells is selected (column may vary) it will copy the selected range and paste the first part of the string before the comma into the cell to the first right of the original value and the next part in the second cell to the right.
'-36.759327,141.847336 = -36.759327 | 141.847336 (Works fine)
'-36.759327, 141.847336 = Run-time error ‘5’:
The following macro works well but only works on a single cell (i.e. ActiveCell) at a time and if there is a space after the comma a Run-time error ‘5’: occurs.
Can the following macro be modified to cope with either no space after the comma or if there is a space.
Is there a better solution that someone could offer...
Sub zx()
'http://superuser.com/questions/352253/how-do-i-separate-a-comma-separated-list-into-two-columns-in-excel
Dim a() As String
Dim v As Variant
Dim i As Long
Dim j As Long
a = Split(ActiveCell.Value, " ")
ReDim v(1 To UBound(a) + 1, 1 To 2)
For i = 1 To UBound(a) + 1
j = InStr(a(i - 1), ",")
v(i, 1) = Val(Left(a(i - 1), j - 1))
v(i, 2) = Val(Mid(a(i - 1), j + 1))
Next
ActiveCell.Offset(0, 1).Resize(UBound(a) + 1, 2) = v
End Sub
I have recorded a macro that copes with or without spaces... but applies to range B8 only:
Sub Macro2()
'
Selection.TextToColumns Destination:=Range("B8"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub
Regards,
Dave T