Importing Excel when one or more cells contain multiple data

[Bill]

I have an excel spreadsheet that has multiple cells with some cells
containing multiple data values separated by the following symbol ý. Here is
what a single line of the spreadsheet looks like.

CODE INVOICE DATE DESCRIPTION GL Account Line
Total
CAROKC 195879 01/04/10 RiceýCornýCandy 4011ý4025ý4027 1.00ý5.00ý11.47

I need to make two tables:
Invoice Header: (this one I know how to do.)
CODE INVOICE DATE
CAROKC 195879 01/04/10

Invoice Detail: (I need help here)
INVOICE DESCRIPTION GL Account Line Total
195879 Rice 4011 1.00
195879 Corn 4025 5.00
195879 Candy 4027 11.47

I am no code wizard and haven't worked with code in over a year, so I really
need something that is easy to understand.

Thanks Bill

 

[Jeff Boyce]

Bill

Have you looked into using Excel functions in Excel to parse those
multi-fact fields into separate columns?

And do all of the rows have the same number of concatenated facts, or might
you have 2 facts in one record and 3 facts in another and 4 facts in a third
and ...?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Bill]

Jeff

Yes the number changes from row to row. One row might just have one but the
next might have five. I solved the problem serveral years back but have since
lost the database with the code. I remember having to identify how many ý
were in the cell so I could do a loop. I also used the another function to
give the location of each ý. I then used the Mid function to grab each piece
of information.

I don't remember what funtion I used to count the number of ý in the string.
Any suggestions?

Bill