Separating Data

[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 category (date,
amount, vendor name and description.)

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

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

 

[Jeff Boyce]

You provided an example of what you want.

What about an example of what you have to start with (I didn't follow your
explanation)?

If you are saying that all those pieces of data are all piled together, how
do you know where to break them apart? For example, your data might use
commas to separate the pieces (not a good idea if you are storing a string
like "$2,000"!).

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bookmdano]

The data is currently displayed in a row, of which there are 3,000 rows of
data.
Each row looks like:

12/01/06 $2,000.00 Cisco Switch for new network

I want to have each of those items in their own column. The data is not
separated by commas. It appears that they are tab delimited.

Thanks for your input.

 

[Jeff Boyce]

I suspect you can link to the Excel source from within Access (Get External
Data; Link) and tell Access that the fields are tab-delimited.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Whoops! Hold on a second...

I was thinking of a simple text file, tab-delimited.

You might be able to export that Excel data to a tab-delimited text file,
then do what I suggested in my previous email.

Or you might be able to import/link that Excel file and use the Split()
function (depending on Access version).

Regards

Jeff Boyce
Microsoft Office/Access MVP