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

  • Thread starter Thread starter AA Smith
  • Start date Start date
A

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.
 
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
 
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
 
Ken

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

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

Dick Smith
Max said:
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
-----------------------------------------
AA Smith said:
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
Click to expand...
guidance
in
accomplishing this task.
Click to expand...
Click to expand...
 
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.
 
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
 
Back
Top