Beesource Beekeeping Forums banner

1 - 4 of 4 Posts

·
Registered
Joined
·
2,264 Posts
Discussion Starter #1
I have the following problem:

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
 

·
Registered
Joined
·
114 Posts
I actually dont think you need any code here to do this. Under Data click on "text to columns" then click on "other" and enter ý in the box. This will put all the data in separate columns. From here you can put it together the way you want using the "concatenate" function.

I think that should do it, if I understand what you want correctly. If this doesnt make sense let me know, I basically live in excel.
 

·
Premium Member
Joined
·
3,097 Posts
Get Microsoft Access for Dummies. It saved me when I had to do some D-basing and I've used it for years since.
 

·
Registered
Joined
·
2,264 Posts
Discussion Starter #4 (Edited)
Text to columns is pretty cool. I have never used that function. Unfortunately it won't work for me, because every cell that has the ý can have any number of them. I did solve this problem around two years ago and code is the only way to do it. Unfortunately I no longer have that data base to copy my code from.:cry:

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.
l
 
1 - 4 of 4 Posts
Top