Separate Data in a Column

[Bookmdano]

I have an old excel file that has mutiple data in each of the rows. I would
like to separate the data and create a column for each. Each category

ex. Date, Amount, Vendor, Description
row 1: 12/02/06 $2,000 Cisco Swtich for new Network

Any suggestions? I am a novice so be warned.....

 

[Dale Fye]

You need to be a little more explicit. From the sample data you provided, I
cannot tell what info you want to split.

 

[Bookmdano]

I want to create a column for the date, amount, vendor name, and description.
The current excel file has all that data in one row and I have over 3,000
rows of similar data.

 

[Dale Fye]

Can you export this from Excel as a tab delimited text file? If so, then
reading it into Access will be as simple as creating a simple import
specification.

If not, do you have a table which lists the various vendors? It would be
easy to parse this on spaces, but if your vendor name is more than one word,
you will not know where to put the break between Vendor and Description.
With an approved vendor list table, then you could check those last two text
fields and split them based on the vendors list (would involve some code),
but could have problems is you had two vendors with the same first name
("SmithBrothers" and "SmithBrothers Widgets").

You might also want to post this message to the Import/Export data newsgroup.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[John W. Vinson]

I have an old excel file that has mutiple data in each of the rows. I would
like to separate the data and create a column for each. Each category

ex. Date, Amount, Vendor, Description
row 1: 12/02/06 $2,000 Cisco Swtich for new Network

Any suggestions? I am a novice so be warned.....

If your current "spreadsheet" design has columns like Date1, Amount1, Vendor1,
Description1, Date2, Amount2, Vendor2, Description2, etc. (you don't actually
SAY so I'm guessing here!!!), a "Normalizing Union Query" will work. Please
post back with the actual structure of your spreadsheet so we can give you
relevant advice.

 

[Jellifish]

Is the data all in a single column within Excel?