Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MSExcel--words in a cell separated by comma need to be moved into.
I have a Excel spreadsheet that has complete addresses in one cell. (ie 123
Washington Street, Seattle WA 98111). I wish to put each item in separate cells for use in a database. Is there an easy way to do this using a function or ?? |
#2
|
|||
|
|||
DataText to columns
-- Kind Regards, Niek Otten Microsoft MVP - Excel "Gary in Alaska" <Gary in wrote in message ... I have a Excel spreadsheet that has complete addresses in one cell. (ie 123 Washington Street, Seattle WA 98111). I wish to put each item in separate cells for use in a database. Is there an easy way to do this using a function or ?? |
#3
|
|||
|
|||
Ok, Here is a solution that I have used many times. It is a bit of an art
form. 1st. Get rid of any column headings that you may have. We want to have only the address that I am assuming are in cells A1, A2, etc. 2nd. Select Edit, Replace. Then type in a single space in the Find What box. In the Replace box type in about 20 spaces. (This will vary of course but you'll get the idea as you try it) 3rd. With column A highlighted. Select Data, Text to Columns. 4th. On the wizard choose Fixed Width. Next. 5th. Now you will see lines that are guessing as to where you want to break the data. Move the lines with your mouse. You will need to scroll down and make sure that you are not cutting off any data. Do this for every field. Click Next. 6th. In the next window you might want to choose column B as your destination. That way your original data will still be intact and you can delete column A later. Click Finished. 7th. Now you will see your excel spreadsheet and if all went well your data will be in separate columns. If all looks wonderful delete column A (your original data) and insert a row above #1 and that will be for your column headings. So you database can know FirstName LastName, Street, City, Etc. Of course always be save and play with a copy of your data so that you won't do irreversable damage to the original. Hope this helps, Erin "Gary in Alaska" wrote: I have a Excel spreadsheet that has complete addresses in one cell. (ie 123 Washington Street, Seattle WA 98111). I wish to put each item in separate cells for use in a database. Is there an easy way to do this using a function or ?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula for seperating two words in the same cell. | New Users to Excel | |||
How do I transpose Comma Separated Data in each cell and delete t. | Excel Discussion (Misc queries) | |||
How do i merge data in a row of cells to be comma separated in on. | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
Count the number of words in a cell! | Excel Worksheet Functions |