How to Remove Extraneous Spaces From a .doc Roster Rile & Paste Into Excel 2002/XP

[AA Smith]

I have a roster with several thousand names in a .doc file. It is organized
by Last Name first, followed by a variable-length string of spaces, then by
First and Middle Name together, followed by another variable-length string
of spaces, then finally the four-digit year the members joined.

I need to transfer the data in the ,doc file into an Excel Worksheet with
the Last Names (with no trailing spaces) appearing in Column A, The First
and Middle Names (with no trailing spaces)appearing in the B column, and
finally the 4-digit year appearing in the C column in the Excel Worksheet.

I'm confident that there's a clean way to do this, however, the method
escapes me at present. So, I shall very greatly appreciate your guidance in
accomplishing this task.

 

[Max]

Try this:

In Word
----------

Click File > Save As

Under "Save as type:" choose "Text Only with Line Breaks" > Ok

[File saved as name: MyName.txt, say]

In Excel
---------
Open the file MyName.txt

This will invoke the Text Import Wizard automatically

"Fixed width" will be checked

Click Finish

 

[Ken Wright]

Dump it in as is into a single column in Excel, and then do the tidy up in
Excel. Use Dave McRitchie's Trimall macro to clean it up first:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

and then select the entire column and use Data / Text To columns / Delimited /
Space as delimiter to chop it into the 3 columns.

 

[Gord Dibben]

AA

Select your column of roster data. Make sure you have at least 2 empty
columns to the right of this column.

Try Data>Text to Columns>Fixed Width>Next>d-click on the break-line between
the first name and initial to remove it. Finish and you should be good.

Gord Dibben Excel MVP

 

[Gord Dibben]

Ken

Assuming a space between First name and Middle name, space-delimited would
give 4 columns. N'est ce pas?

Gord

 

[AA Smith]

Thanks, Max, for your help. I shall give your suggestion the ol' college
try.
--
With kindest regards,

Dick Smith

Try this:

In Word
----------

Click File > Save As

Under "Save as type:" choose "Text Only with Line Breaks" > Ok

[File saved as name: MyName.txt, say]

In Excel
---------
Open the file MyName.txt

This will invoke the Text Import Wizard automatically

"Fixed width" will be checked

Click Finish

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

I have a roster with several thousand names in a .doc file. It is organized
by Last Name first, followed by a variable-length string of spaces, then by
First and Middle Name together, followed by another variable-length string
of spaces, then finally the four-digit year the members joined.

I need to transfer the data in the ,doc file into an Excel Worksheet with
the Last Names (with no trailing spaces) appearing in Column A, The First
and Middle Names (with no trailing spaces)appearing in the B column, and
finally the 4-digit year appearing in the C column in the Excel Worksheet.

I'm confident that there's a clean way to do this, however, the method
escapes me at present. So, I shall very greatly appreciate your

guidance

in
accomplishing this task.

 

[AA Smith]

Thanks, Ken for this veritable wealth of information. I'm confident that
with all the feedback I'm getting on this, that I shall indeed find a
solution. And I shall indeed put your suggestions and the macro to work.

 

[AA Smith]

That's true, Gord. Thanks for pointing that out.

 

[AA Smith]

Thanks, again, Gord! This seems simple enough. I'll put it to use.

 

[Max]

You're welcome, Dick !
Thanks for the feedback.

 

[Ken Wright]

followed by a variable-length string of spaces, then by
Took it literally, ie to mean no space between them. Worst case though was
concatenating the two columns back together