Parsing string into separate columns of a table

[EndersG]

I have a Microsoft Access table with the following columns: A,B,C,D,E,F.
In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck"
What I would like to do is parse this string as such:

Column B:"Al"
Column C:"Peggy"
Column D:"Kelly"
Column E:"Bud"
Column F:"Buck"

Is there a simple VB funtion to accomplish this?

 

[WayneRyan]

EndersG,

Use an update query with a reference to a Public Function.

Update YourTable
Set    ColumnB = GetColumn([ColumnA], 1),
       ColumnC = GetColumn([ColumnA], 2),
       ColumnD = GetColumn([ColumnA], 3),
       ColumnE = GetColumn([ColumnA], 4),
       ColumnF = GetColumn([ColumnA], 5)


Public Function GetColumn(strText As String, intColumn As Integer)
Dim varArray As Variant
varArray = Split(strText, ",")
Select Case intColumn
   Case 1
      If UBound(varArray) >= 1 Then
         GetColumn = varArray(1)
      Else
         GetColumn = ""
      End If
   Case 2
      If UBound(varArray) >= 2 Then
         GetColumn = varArray(2)
      Else
         GetColumn = ""
      End If
   Case 3
      If UBound(varArray) >= 3 Then
         GetColumn = varArray(3)
      Else
         GetColumn = ""
      End If
   Case 4
      If UBound(varArray) >= 4 Then
         GetColumn = varArray(4)
      Else
         GetColumn = ""
      End If
   Case 5
      If UBound(varArray) >= 5 Then
         GetColumn = varArray(5)
      Else
         GetColumn = ""
      End If
   End Select
End Function

hth,
Wayne