Need help getting data from Word to Excel

[JM]

I'm novice level with Word and Excel, so excuse what may be a basic
question/problem that I've got.

I asked an employee to enter lots of data into Excel. The data is company
name, address, contact name, and phone number. I requested that each type
of data be entered into different columns. Instead, the employee used MS
Word 2003. To compound the issue, the employee did not use commas, tabs, or
any other delimiter or formatting method that might allow me easily to get
the data over into Excel (by *easily* I mean using a technique that I know
; ) The data in Word simply is separated by random numbers of spaces.
Nothing lines up, either, so I can't use the manual column markers in
Excel's Open As wizard.

What are my options for getting the data into Excel with the 4 categories in
separate columns? There are several hundred rows of data.

Thank you,

JM

 

[Steve Yandl]

JM,

Are you fairly sure that the emplyee used two or more spaces between each
entry in a row? Are you also confident that there are only single spaces
between characters within a field (for example, he/she didn't put two spaces
between the state abbreviation and zip code in the address field)? If so,
here is one possible option.

Make a backup copy of the Word document before the experiment!

Open the Word document and either press Ctrl plus the letter "h" or click
Edit > Replace

In the replace window, click the "More" button and put a check next to "Use
wildcards"

In the "Find What:" box, type in
[ ]{2,}
note, that is square brackets with a single space between, followed
immediately by curly braces around the number two and a comma.
In the "Replace With:" box, type in
^t
Now, click the "Replace All" button. Every instance where Word finds two or
more spaces in sequence will be replaced by a single tab character. I'd
probably select all the text, click the 'Table' drop down menu and choose to
convert text to a table. This way you can easily check for any odd results
and correct before moving from Word and then do a simple copy > paste to
move the table data into Excel.

Steve

 

[wjohnson]

Follow-up to Steves Reply
Sometimes text will line up "better" than you think if you do a "select
all and convert all text to a "Courier" Font.
Next time - have the people who enter the text into a WORD Table and
then you can usually copy and paste directly into EXCEL.
If you cannot solve the problem easily - attach a file and someone
might be able to see away to get tabs inserted where the various fields
or excel columns should be.

 

[JM]

Steve, thank you very much! The result wasn't perfect, but it's so darn
close. I'm thrilled!

Other than hands-on training, how can I accelerate my learning of MS Office
products like Word and Excel? I can get around them good enough to be
productive, but I'd love to have a more in-depth understanding of some of
their more advanced and powerful features. Input appreciated.

Thanks again.

JM

JM,

Are you fairly sure that the emplyee used two or more spaces between each
entry in a row? Are you also confident that there are only single spaces
between characters within a field (for example, he/she didn't put two
spaces between the state abbreviation and zip code in the address field)?
If so, here is one possible option.

Make a backup copy of the Word document before the experiment!

Open the Word document and either press Ctrl plus the letter "h" or click
Edit > Replace

In the replace window, click the "More" button and put a check next to
"Use wildcards"

In the "Find What:" box, type in
[ ]{2,}
note, that is square brackets with a single space between, followed
immediately by curly braces around the number two and a comma.
In the "Replace With:" box, type in
^t
Now, click the "Replace All" button. Every instance where Word finds two
or more spaces in sequence will be replaced by a single tab character.
I'd probably select all the text, click the 'Table' drop down menu and
choose to convert text to a table. This way you can easily check for any
odd results and correct before moving from Word and then do a simple copy

paste to move the table data into Excel.

Steve

I'm novice level with Word and Excel, so excuse what may be a basic
question/problem that I've got.

I asked an employee to enter lots of data into Excel. The data is
company name, address, contact name, and phone number. I requested that
each type of data be entered into different columns. Instead, the
employee used MS Word 2003. To compound the issue, the employee did not
use commas, tabs, or any other delimiter or formatting method that might
allow me easily to get the data over into Excel (by *easily* I mean using
a technique that I know ; ) The data in Word simply is separated by
random numbers of spaces. Nothing lines up, either, so I can't use the
manual column markers in Excel's Open As wizard.

What are my options for getting the data into Excel with the 4 categories
in separate columns? There are several hundred rows of data.

Thank you,

JM

 

[Steve Yandl]

JM, you're welcome. I'm glad it worked.

If you're like me, you learn far more with a project relevant to you actual
needs than going through a tutorial. I like to have a few books around for
reference but find that struggling through my own projects and then scanning
newsgroups for problems similar to the obstacles I run into is the fastest
way for me to learn (and retain) the information.

In addition to newsgroups, there are some great web sites for Office
applications. Some of my personal favorites for Excel are:
http://www.cpearson.com/excel.htm
http://www.j-walk.com/ss/
http://www.contextures.com/tiptech.html

For Word, you might take a look at:
http://word.mvps.org/FAQs/index.htm
http://www.shaunakelly.com/word/index.html
http://homepage.swissonline.ch/cindymeister/


Steve

Steve, thank you very much! The result wasn't perfect, but it's so darn
close. I'm thrilled!

Other than hands-on training, how can I accelerate my learning of MS
Office products like Word and Excel? I can get around them good enough to
be productive, but I'd love to have a more in-depth understanding of some
of their more advanced and powerful features. Input appreciated.

Thanks again.

JM

JM,

Are you fairly sure that the emplyee used two or more spaces between each
entry in a row? Are you also confident that there are only single spaces
between characters within a field (for example, he/she didn't put two
spaces between the state abbreviation and zip code in the address field)?
If so, here is one possible option.

Make a backup copy of the Word document before the experiment!

Open the Word document and either press Ctrl plus the letter "h" or click
Edit > Replace

In the replace window, click the "More" button and put a check next to
"Use wildcards"

In the "Find What:" box, type in
[ ]{2,}
note, that is square brackets with a single space between, followed
immediately by curly braces around the number two and a comma.
In the "Replace With:" box, type in
^t
Now, click the "Replace All" button. Every instance where Word finds two
or more spaces in sequence will be replaced by a single tab character.
I'd probably select all the text, click the 'Table' drop down menu and
choose to convert text to a table. This way you can easily check for any
odd results and correct before moving from Word and then do a simple copy

paste to move the table data into Excel.

Steve

I'm novice level with Word and Excel, so excuse what may be a basic
question/problem that I've got.

I asked an employee to enter lots of data into Excel. The data is
company name, address, contact name, and phone number. I requested that
each type of data be entered into different columns. Instead, the
employee used MS Word 2003. To compound the issue, the employee did not
use commas, tabs, or any other delimiter or formatting method that might
allow me easily to get the data over into Excel (by *easily* I mean
using a technique that I know ; ) The data in Word simply is separated
by random numbers of spaces. Nothing lines up, either, so I can't use
the manual column markers in Excel's Open As wizard.

What are my options for getting the data into Excel with the 4
categories in separate columns? There are several hundred rows of data.

Thank you,

JM