How do I split content in one cell into seperate cells?

[Guest]

I have an Access 2003 DB where one of the columns contains multiple names
(10-15), seperated by special seperators that look like rectangular boxes.

I want to sperate each name so that each appears in individual row/column
alongside.

Kindly help.

Thanks,

A. Singh

 

[Allen Browne]

Write a VBA function that opens 2 records:
- the source table, with the concatenated field;
- the target table that you want to add records to.

Use the Split() function to parse the elements into an array.
Loop through the array elements from LBound to Ubound.
For each one, use AddNew and Update to add the records to the target table.

 

[Guest]

Create a function in a module, in that example the seperator between the
names will be ~

Function MySplit(MyStr As String, MyLocation As Integer)
On Error goto MySplit_Err
MySplit = Split(MyStr, "~")(MyLocation)

Exit Function
MySplit_Err:
MySplit = ""
End Function

In the Query you can use this spit function to split the field

Select MySplit([FieldName],0) As FirstName, MySplit([FieldName],1) As
SecondName, MySplit([FieldName],2) As ThirdName From TableNAme

 

[Guest]

Thanks.

A. Singh

Write a VBA function that opens 2 records:
- the source table, with the concatenated field;
- the target table that you want to add records to.

Use the Split() function to parse the elements into an array.
Loop through the array elements from LBound to Ubound.
For each one, use AddNew and Update to add the records to the target table.

 

[E W]

use combinations of mid, left and righ functions to split out info. usiing
the "find" function to find a particular character that reoocurs in all the
data can help locate the starting character for the "mid" function. also the
"len" function can help to determine where to end or start a mid, left or
right function. help on these functions best found in google, ms help files
on functions are amazingly sparse and difficult to locate.