String Manipulation.

[Eric]

I have a column that contains several string values that
are seperated by a semicolumns, i.e.


yellow;red;green
black;red;blue
orange;yellow

I need a function that can write these different strings
into another table but each string value needs to be
in seperate columns. Have no idea who to start on this.
Any help is most appreciated!
TIA.

 

[Wayne Morgan]

You may want to try the Split function to get started. It will return a one
dimensional array containing each value.

I just tried the following as a test. I had to use the ReDim, setting the 4
in the Dim statement wouldn't compile.

Public Sub TestSplit()
Dim strResult() As String, i As Byte
ReDim strResult(4)
strResult() = Split("this;is;a;test", ";")
For i = 0 To 3
Debug.Print strResult(i)
Next i
End Sub

 

[Tim Ferguson]

I need a function that can write these different strings
into another table but each string value needs to be
in seperate columns.

Reading between the lines, you might be better served by writing the values
into different _records_ in a new table....

HTH

Tim F

 

[John Nurick]

Tim's probably right, but either way this function can be used to
extract the individual items from the string.

Public Function GetFieldPart( _
Value As Variant, _
Delimiter As String, _
Item As Long) As Variant

Dim arItems As Variant
Dim varTemp As Variant

varTemp = Null
If Not IsNull(Value) Then
If VarType(Value) = vbString Then
If InStr(CStr(Value), Delimiter) = 0 Then
'No delimiter so only one item
If Item = 1 Then varTemp = Value
Else
'Delimiters found, split into array
arItems = Split(Value, Delimiter)
If (Item > 0) And (Item - 1 <= UBound(arItems)) Then
varTemp = arItems(Item - 1)
Else
varTemp = Null
End If
End If
End If
End If
GetFieldPart = varTemp
End Function