If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data Storage
I would like to create two cells (in ACCESS) from one EXCEL cell . For
example, I inherited a 10,000 record EXCEL worksheet that I will convert to an ACCESS table. The EXCEL worksheet contains about 10,000 lines (each line is one record) and each line has an address. The address (in EXCEL) is shown as 123 Maple and is in one cell. I would like to import the EXCEL address worksheet and create an ACCESS table called "ADDRESS". This table would contain one line (one record) for each line in the EXCEL worksheet. But I would like ACCESS to have two cells, one for the number part of the address and one for the street name part of the address. So, for the EXCEL that has 123 Maple (all one cell) I would have an ACCESS table and each record would include two cells. Cell one would contain the number 123 and cell two would contain the street name, Maple. Thank you -- Doug -- |
#2
|
|||
|
|||
Hi Doug
You posted this question back nearly two weeks ago. Did you not see john Vinson's reply? He said: On Sat, 19 Mar 2005 14:59:02 -0800, "Doug" I'd suggest using File... Get External Data... Import to import the spreadsheet. Then go into table design view and add the AddressNumber and Street fields. Run an Update query updating AddressNumber to Left([ADDRESS], InStr([ADDRESS], " ") - 1) and Street to Mid([ADDRESS], InStr([ADDRESS], " ") + 1) Note that some addresses may give odd results: i.e. 312 1/2 Maple St will put 1/2 Maple St in the Street field; and an address like PO BOX 123 will put PO in the street number, and BOX 123 in the STREET. Manual editing may be required! John W. Vinson[MVP] -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Doug" wrote in message ... I would like to create two cells (in ACCESS) from one EXCEL cell . For example, I inherited a 10,000 record EXCEL worksheet that I will convert to an ACCESS table. The EXCEL worksheet contains about 10,000 lines (each line is one record) and each line has an address. The address (in EXCEL) is shown as 123 Maple and is in one cell. I would like to import the EXCEL address worksheet and create an ACCESS table called "ADDRESS". This table would contain one line (one record) for each line in the EXCEL worksheet. But I would like ACCESS to have two cells, one for the number part of the address and one for the street name part of the address. So, for the EXCEL that has 123 Maple (all one cell) I would have an ACCESS table and each record would include two cells. Cell one would contain the number 123 and cell two would contain the street name, Maple. Thank you -- Doug -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I get 3 series in sync with the x-axis? | zizbird | Charts and Charting | 10 | October 25th, 2004 01:23 PM |
Data Source issues. ??data.access.pages | Phil | Database Design | 2 | October 11th, 2004 02:42 AM |
Data Source issues | Philippe | Database Design | 1 | October 10th, 2004 09:45 PM |
Export data from OLE object in PowerPoint | Brett Ellingson | Powerpoint | 8 | August 25th, 2004 12:28 AM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |