Hi Agus,
One complication here is that it's not always easy to define a "word".
For instance, is "Mary-Ellen" one word or two?
I've pasted two VBA functions at the end of this message. One assumes
that "words" are separated by spaces; the other assumes that "words" are
substrings that contain only alphanumeric characters, underscores and
hyphens.
You can use either function in a calculated field in a query. E.g.
Word: ParseWords([MyField], 0)
will return the first word in MyField. The thing to do is to build an
append query that creates a record in your other table for the first
word of each record in Table1. After running it, modify it so it gets
the second word and run it again. And so on.
It doesn't matter if some records in Table1 contain more words than
others: the functions just return Null if you specify a number greater
than the actual number of words in the field.
Dear All,
I have a text field in Table1.
If for example, the field contain "Mary had a little lamb", I want to have
Word1 = Mary
Word2 = had
Word3 = a
Word4 = little
Word5 = lamb
I am going to put every word as a single record in another table.
Anyone can help?
Rgds,
Agus Budianto
'START OF CODE =============================================
Public Function ParseItems( _
List As Variant, _
Item As Long, _
Optional Separator As String = " " _
) As Variant
'Returns the specified item from a list of "words" separated
'by a space (or by Separator).
'Counting starts at zero.
'Returns Null if the list is too short.
Dim arWords As Variant
If IsNull(List) Then
ParseItems = Null
Exit Function
End If
arWords = Split(CStr(List), " ", Item + 2)
If UBound(arWords) < Item Then
ParseItems = Null
Else
ParseItems = arWords(Item)
End If
End Function
Public Function ParseWords( _
List As Variant, _
Item As Long) As Variant
'Returns the specified "word" from a string.
'Counting starts at 0.
'A "word" is a substring consisting only of alphanumeric characters,
'hyphens and underscores.
'By John Nurick, 2005
Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
If IsNull(List) Then
ParseWords = Null
Exit Function
End If
Set oRE = CreateObject("VBScript.Regexp")
With oRE
.Pattern = "\b[-0-9A-Z_]+\b"
.Global = True
.IgnoreCase = True
.Multiline = True
End With
Set oMatches = oRE.Execute(CStr(List))
If oMatches.Count <= Item Then
ParseWords = Null
Else
ParseWords = oMatches(Item)
End If
Set oRE = Nothing
End Function
'END OF CODE ===============================
