Data Mess

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Using Word/Excel 2003
I have a document that consists of 50 pages. The text in
this document is not in any type of order. I want to be
able to import this text from Word to Excel and when in
Excel, transfer the data to (4) columns. How can this be
done if the data is as follows? The first group of data
has 4-6 characters. Then there's space and there's a last
name (space) first name (space) and middle initial. Then
there's 15-25 spaces and then 2 characters (a state
abbreviation)- In the 4th group of data (for the 4th
column)There's 1-2 spaces and then the data, that consists
of 5-10 characters. How can I import this to Excel and
place it in 4 columns if the data is all over the place?
The data is not in table form.

HELP!
 
From the Edit menu, select Replace and click on the More button and check
the Use Wildcards box. Then in the Find what control, enter a space
followed by {1,} and in the Replace with control, enter ^t. Then click on
replace all. This will replace each space or group of spaces with a tab
character.

Now, if you select and copy the text and then paste it into Excel,
everything will be inserted into columns.

Now I believe that you have more items than you want columns, but once in
Excel, it is easy to combine the contents of two of more columns using a
formula that refers to each of the cells that you want to combine with an &
separating them.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
 
Thanks Doug - That worked Great! Although, when I enter
the space followed by the {1,}, what does the {1,} do?
Thanks again
 
It tells the search engine to look for and replace one or more spaces. For
a full explanation of the use of wildcards, see the article "Finding and
replacing characters using wildcards" at:

http://word.mvps.org/FAQs/General/UsingWildcards.htm

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
 
Back
Top