extract multiple values from a cell to a separate table?

[benlogo]

Hello all,

I have imported an Excel table with a column which has several values in one cell separated by "\". In my case names of institutions afiliated with a patent. Example of the format of a single cell: MASSACHUSETTS INSTITUTE OF TECHNOLOGY (US)\ RIVE TECHNOLOGY INC (US)

In order to normalize the Access database I would like to extract the institutions to a separate table (institutions) and that the patent is related to both institutions.



Can anyone help me? I am using Access 2007.

Thanks in advance!

[ranman256]

this only works if EVERY cell in field1 has the \ in it.

select
left([field1],instr([field1],"\")-1) as LEFTNAME,
mid([field1],instr([field1],"\")+1) as RIGHTNAME
from table

[orange]

see this link for an example of the Split() function.

[benlogo]

thanks for the reply!
unfortunately only the cells with more than one company have the \ in it, but nearly half the cells of the column (about 1500) have only one company in this field.
Is there any way I can still do it?

[orange]

The routine I showed will still work.
You will need to adjust the code to get new incoming records, but the Split function will still work