Monday, November 30, 2009

VBA Macro - Split/Parse CSV Strings


Option Explicit

Public Sub splitCSV(targetCell As range)

Dim row As Integer
row = targetCell.row

Dim col As Integer
col = targetCell.Column

Dim source As String
source = targetCell.Text

Dim count As Integer
count = 0

Dim startPos As Integer
startPos = 1

Dim nextCommaPos As Integer

Dim exitLoop As Boolean
exitLoop = False
While (exitLoop = False)

Dim commaPos As Integer
commaPos = InStr(startPos, source, ",")

Dim token As String

If (commaPos = 0) Then
exitLoop = True
Else

If (startPos = 1) Then
commaPos = 0
End If

nextCommaPos = InStr(commaPos + 1, source, ",")

If (nextCommaPos = 0) Then
nextCommaPos = Len(source) + 1
End If

token = Mid(source, commaPos + 1, nextCommaPos - commaPos - 1)
count = count + 1

Cells(row, col + count).Value = token

If (startPos <> 1) Then
startPos = commaPos + 1
Else
startPos = 2
End If


End If
Wend

End Sub

Public Sub SplitSelection()

Application.ScreenUpdating = False

Dim sel As range
Set sel = Selection

Dim row As Integer
For row = sel.row To (sel.row + sel.Rows.count - 1)

Dim targetCell As range
Set targetCell = Cells(row, sel.Column)
Call splitCSV(targetCell)

Next row

Application.ScreenUpdating = True

End Sub

No comments:

Post a Comment

comment: