Seperate words in a field

[Okstate student]

I have data that has information in a field that I need to separate.

Example: Jimmy Ray (2004) (director) (producer)
Jenny Rich (2000) (Actress)

From this example I need to separate the items in parentheses from the name
and enter them all in different columns. Is there a query or other possible
way to do this?

Thanks for any help given.

 

[ABC Seamless Wayne]

As I see it this would be a 2-step process.. First you would separate all
words:

Divide text across cells

Select the range of cells that contains the text values. The range can be
any number of rows tall, but no more than one column wide.
Note There must be one or more blank columns to the right of the selected
column or the data to the right of the selected column will be overwritten.

On the Data menu, click Text to Columns.
Follow the instructions in the Convert Text to Columns Wizard to specify how
you want to divide the text into columns

Next Combine First and Last name:

Use the ampersand (&) operator or the CONCATENATE function to do this task.

If you need additional help with these steps please post back. This info
came from Office Help.

 

[ABC Seamless Wayne]

Sorry!! I thought I was in Excel Still. Please Disregard.

 

[Bob Quintal]

=?Utf-8?B?T2tzdGF0ZSBzdHVkZW50?=

I have data that has information in a field that I need to
separate.

Example: Jimmy Ray (2004) (director) (producer)
Jenny Rich (2000) (Actress)

From this example I need to separate the items in parentheses from
the name and enter them all in different columns. Is there a
query or other possible way to do this?

Thanks for any help given.

Query-based solutions for this type of problem are a pain to
implement due to an inconsistant number of sub-fields.

A VB function which takes the full string plus an offset number and
returns the value in the subfield would probably be faster as well.

Public Function fSplitOnParenthesis(strInput As String, _
iOffset As Integer _
) As Variant
' Variant required to handle nulls
Dim iPtr As Integer
Dim iLen As Integer
Dim iStartPos As Integer
Dim iEndPos As Integer
Dim bFailed As Boolean

If iOffset = 0 Then 'User wants Name
iLen = InStr(1, strInput, "(") - 1
If iLen < 1 Then ' No ( so return whole string
fSplitOnParenthesis = strInput
Else
fSplitOnParenthesis = Left(strInput, iLen)
End If
Else ' we need to find the position of the Nth Sub-field
For iPtr = 1 To iOffset
iStartPos = InStr(iStartPos + 1, strInput, "(")
If iStartPos = 0 Then bFailed = True
Next
If Not bFailed Then
iEndPos = InStr(iStartPos, strInput, ")")
If iEndPos = 0 Then bFailed = True
End If
If Not bFailed Then
fSplitOnParenthesis = Mid(strInput, _
iStartPos + 1, iEndPos - iStartPos - 1)
End If
End If
End Function