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 ??

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 ??

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 ??