Moving Text from Word to Excel (and having it set into 2 columns)

I have a Word Document that has the follow format. . .

word       ,     word
word   ,      word
word             ,  word
word    ,  word

.. . .  It’s basically a two page list of 2 words separated by a comma per 
line. But the spacing between the words is sporadic, and likewise the comma 
doesn’t have any defined (fixed) position between the words.

I’d like to export this list into Excel so that Excel will read it as 1 row 
of 2 columns . Do I need to first standardize the spacing between the words 
and comma, or is there a better way to do this?

I’m not sure what the best approach should be. I have never created a CSV 
doc in Word before, and for that matter I can't ever remembering a time where 
I imported from Word into Excel either. So, I would love to get any feedback 
on this situation.


Take Care, and Thanks,

Jim

[ for those curious I got this of of the internet. the comma's used to be 
dashes but I changed them over to comma's thinking that would be the first 
step in the right direction. Obviously I don't know what the next steps are. 
]  :-)  
0
Utf
4/2/2010 2:24:01 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1493 Views

Similar Articles

[PageSpeed] 49

I've managed to stumble upon a solution. By using Text to Columns. 

This works nicely but now my 2nd column entry's have an empty space before 
each word. 

Anybody know How I can get rid of the empty space before the word? 


Thanks, 

Jim 

0
Utf
4/2/2010 2:48:05 AM
One solution:
In Text-to-Columns, define the space as a delimiter, and check off: Treat 
consecutive delimiters as one.

Fred

"Jim D." <JimD@discussions.microsoft.com> wrote in message 
news:7BDE6FF2-E5A1-4621-B835-4F0294D3DD1F@microsoft.com...
> I've managed to stumble upon a solution. By using Text to Columns.
>
> This works nicely but now my 2nd column entry's have an empty space before
> each word.
>
> Anybody know How I can get rid of the empty space before the word?
>
>
> Thanks,
>
> Jim
> 

0
Fred
4/2/2010 2:52:35 AM
You can remove the unnecessary spacess by using Wildcards in Ms-Word.

For your below query just open your word document and give Cntrl+H (to get 
the Replace Dialog Box.  Now click the More button or (M OR ALT+M) and check 
the Use Wildcards.  

In find what box copy and paste this              [ ]{1,100},
In Replace with box copy and paste this         , 

Now click Replace All or Alt+A.

Now your text is converted From 
word       ,     word
word   ,      word
word             ,  word
word    ,  word

To
word,     word
word,      word
word,  word
word,  word

Once again 
In find what box copy and paste this              ,[ ]{1,100}
In Replace with box copy and paste this         ^t

Again Click the Replace all button.
word	word
word	word
word	word
word	word

Now your word file data structure is converted to excel compatibiltiy now 
you can copy and paste the data in Excel.


For more details about Word Wildcard and its usages go through the below 
websites:-
http://www.gmayor.com/replace_using_wildcards.htm
http://word.mvps.org/faqs/general/usingwildcards.htm

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Jim D." wrote:

> I have a Word Document that has the follow format. . .
> 
> word       ,     word
> word   ,      word
> word             ,  word
> word    ,  word
> 
> . . .  It’s basically a two page list of 2 words separated by a comma per 
> line. But the spacing between the words is sporadic, and likewise the comma 
> doesn’t have any defined (fixed) position between the words.
> 
> I’d like to export this list into Excel so that Excel will read it as 1 row 
> of 2 columns . Do I need to first standardize the spacing between the words 
> and comma, or is there a better way to do this?
> 
> I’m not sure what the best approach should be. I have never created a CSV 
> doc in Word before, and for that matter I can't ever remembering a time where 
> I imported from Word into Excel either. So, I would love to get any feedback 
> on this situation.
> 
> 
> Take Care, and Thanks,
> 
> Jim
> 
> [ for those curious I got this of of the internet. the comma's used to be 
> dashes but I changed them over to comma's thinking that would be the first 
> step in the right direction. Obviously I don't know what the next steps are. 
> ]  :-)  
0
Utf
4/2/2010 2:56:01 AM
If your data is starting from 1st row use the below forumla:-
Paste this formula in C1 cell
=TRIM(B1)
Copy and paste the C1 cell and paste it to the remaining cells of C Column.

If your data is starting from 2nd row use the below forumla:-
Paste this formula in C2 cell
=TRIM(B2)
Copy and paste the C2 cell and paste it to the remaining cells of C Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Jim D." wrote:

> I've managed to stumble upon a solution. By using Text to Columns. 
> 
> This works nicely but now my 2nd column entry's have an empty space before 
> each word. 
> 
> Anybody know How I can get rid of the empty space before the word? 
> 
> 
> Thanks, 
> 
> Jim 
> 
0
Utf
4/2/2010 3:00:01 AM
Hi Jim, you might try the following macro.  Change the word doc and
text file paths/names as necessary.  Also, you'll first need to turn on
the reference to Microsoft Word 12.0 Object Library in the VB Editor of
Excel (Tools -> References -> scroll down and select that library and
click OK).



Code:
--------------------
  

  
Sub wrd2exl()
  Dim wdApp As Word.Application, wdDoc As Word.Document
  
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  If Err.Number <> 0 Then
  Set wdApp = CreateObject("Word.Application")
  End If
  On Error GoTo 0
  
  Set wdDoc = wdApp.Documents.Open("C:\temp\Word1.docx")
  wdDoc.SaveAs Filename:="C:\temp\Word1.txt", _
  FileFormat:=wdFormatText, LockComments:=False, Password:="", _
  AddToRecentFiles:=True, WritePassword:="", _
  ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
  SaveNativePictureFormat:=False, SaveFormsData:=False, _
  SaveAsAOCELetter:=False, Encoding:=1252, InsertLineBreaks:=False, _
  AllowSubstitutions:=False, LineEnding:=wdCRLF
  
  wdDoc.Close False
  wdApp.Quit
  Set wdDoc = Nothing
  Set wdApp = Nothing
  
  With ActiveSheet.QueryTables.Add(Connection:= _
  "TEXT;C:\temp\Word1.txt", Destination:=Range("$A$1"))
  .Name = "Word1"
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .TextFilePromptOnRefresh = False
  .TextFilePlatform = 437
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = True
  .TextFileTabDelimiter = False
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = True
  .TextFileSpaceDelimiter = True
  .TextFileColumnDataTypes = Array(1, 1)
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
  End With
  End Sub
--------------------


-- 
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192428

http://www.thecodecage.com/forumz

0
Paul
4/2/2010 3:09:18 AM
Reply:

Similar Artilces:

not able to move FLAG STATUS column in Outlook 2003
I tried to move FLAG STATUS column from the very right side to the left side when I am in my Inbox. IT WON'T LET ME!! Anyone was able to do that? No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Kenny Shu asked: | I tried to move FLAG STATUS column from the very right | side to the left side when I am in my Inbox. IT WON'T LET | ME!! | | Anyone was abl...

Graphic with text boxes
Hi, I seem to have a problem. I am charting the performance of stocks with the change in recommendations of analysts. So I build a simply line chart (which I have automated). I am including text boxes on top of the graphic which have a line pointing down to a date on the chart line. The attached word file shows an example of what the graphic looks like. The problem is: when I update the graphic and add new price data, the chart line shifts to the left (because I'm charting more data now) but the text boxes and lines do not move with the graphic (I want the boxes and lines to move with the...

OWA login changed after installing service pack 2!
we have exchange 20003 running on windows 2003 server. I applied service pack 2 last night, and found out our remote users were unable to login. what has happend is this: where our local domain name was simpson.com, but our email address was homersimpson.com, before the service pack update a remote user would type their usename, and it would put the domain name in front - as in simpson\bart (if bart was username). NOW when they try to login it shows homersimpson.com\bart and will not log them in, and I have to tell them to type simpson\bart to get in. what has changed, and how do I p...

Possible? Using Word template .dot file as app's doc template?
Just curious... I was wondering if a Word .dot file could be used as a template for my app's document. It would give users the option of customizing the look of output from my program without my having to duplicate a lot of functionality. For them it would be a familiar way to do the formatting. A win/win as they say. Do Word documents have a published format, or are redistributable .dll files available from Microsoft (at extra cost) to do this? Thanks, Rick ...

Has anyone installed CRM 1.2 on a Win Server 2003 DC?
Hi, I want to know if we can install CRM 1.2 on a DC with Windows Server 2003 SP1? If yes, then has somebody faced any performance problem? Thanks and Regards, Harshad ...

Allow comments to be added to protected worksheet?
I have created a worsksheet with selected areas locked and then protected it. My users want to be able to add comments where necessary but are not able to. I cant seem to find anything in the help menu - any ideas? When protecting the sheet "allow users to:" Select unlocked cells and Edit Objects. Note: with edit objects checked, users can insert comments in locked cells if they are allowed to select those. Gord Dibben MS Excel MVP On Wed, 4 Nov 2009 15:00:02 -0800, sue@solotel <suesolotel@discussions.microsoft.com> wrote: >I have created a worsksheet with sel...

Word wrap in excel?
Hi, Is there word wrap functionality in excel? If in cell A1 I have a string of text say 5000 chars long, Is there functionality in excel to wrap this text around when the text reaches the vertical page break? Thanks, DK "David Kennedy" <dkennedy85@hotmail.com> wrote in message news:use8PWeMIHA.484@TK2MSFTNGP06.phx.gbl... > Hi, > > Is there word wrap functionality in excel? > > If in cell A1 I have a string of text say 5000 chars long, > Is there functionality in excel to wrap this text around when the text > reaches the vertical page break? You...

Moving license to a new computer
I bought a powerbook and am giving the kids my old imac. I'd like to move Office to the powerbook and remove it from the imac. Am I going to run into any technical difficulties doing this? In article <do3en0hjuiv1jrgsan1tlf23demblohh9p@4ax.com>, Andrew D <andrew.davilman@mindspring.com> wrote: > I bought a powerbook and am giving the kids my old imac. I'd like to > move Office to the powerbook and remove it from the imac. Am I going > to run into any technical difficulties doing this? Shouldn't. Run the Remove Office application on the iMac, and inst...

Autofilter in Excel 2007
I have a sheet of financial data to which I have applied the autofilter. Clicking on the downarrow in each of the header fields produces a series of options one of which is the filter. This filter box then lists the discrete data for that particular field with an associated checkbox. However, the one field I do want to filter in this manner - "Payees" - displays a Text Filter box with only one choice: Select All. In Excel 2003 all the Payees would be appropriately listed for selection. Que pasa? I want to use this approach to extract records pertaining to selected payees. Hi...

Safe to Move?
Hello, I remember seeing an article about moving $NtServicePackUninstall$ files to free up space. I think it was for Windows 2000 Server. Is it possible to do this for Windows 2003 server? -- Thank you for your help! JYC Hello Mr. JYC, Yes, you can do it and if are sure that there is no need to uninstall it you can delete it. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Please do NOT email, only reply to Newsgroups ** HELP us help YOU!!! http://www.blakjak.demon.co.uk/mul_crss.ht...

Live Mail,I can't move message "unknown Error" appears
-- There are several messages that I want to place in folders, but when I try to move them, I receive the message "Unable to move, Unknown Error". This is more than annoying, as these are important messages from various organizations I belong to. I like to archive these within the confines of Live Mail, and I don't want to hve to go in and out to various folders. Any help will be appreciated. Windows Live Mail Newsgroup. In your newsreader: news://msnews.microsoft.com/microsoft.public.windows.live.mail.desktop On the Web: http://www.microsoft.com/communiti...

Copy or Move row on condition to different and/or multiple sheets
Good afternoon. I'm new to the world of programming in excel but have programmed elsewhere previously. What i am trying to do is to have a todo list setup, ive searched long and hard and can't find anything that quite fits my bill that i can replicate and alter to work so am asking for some assistance. I have a workbook with six sheets, All Jobs, Evaluation, Authority, In Progress, Completed and a helper sheet. In all jobs i have 9 columns A-I (atm A being spare) that have information that i need to show. In column 'I' i have a dropdown box using the helper sheet for p...

Creating a goto/anchor in excel based on a cell value
Hello, I am creating a financial worksheet for a client in which the user can choose options from a drop down menu. What I want to happen is, based on the option the user chooses, the model sends them to a certain cell on the same sheet. Ex: If user chooses 1 from the drop down, it sends them to cell A4. If the user chooses 2 from the drop down, it sends them to cell A5. Also, I would like to know how to trigger a macro based on a cell value Here is the code I thought might work =if(A1=1, Run_OLM(),"") Any advice on these topics is much appreciated. Thanks in advance. Tushar...

Moving Exchange 2000
Hi, I have a exchange 2000 server running on windows 2000 which I need to move onto a new server/hardware but cannot find techincal documents telling me how to do this. The only documents I can find are on disaster recovery which doesnt seem to cover moving exchange across onto new hardware. Does anybody have some a document ??? Also I would like to run exchange 2000 on server 2003, I'm guessing that this won't be a problem to move it onto new hardware running server 2003 ?? Kind Regards Paul... Use the move-server method detailed here: http://tinyurl.com/ys7e9 -- Neil H...

mail moved from netscape to outlook?
Hello! I would like to know is it possible to move the mails from netscape mail to outlook? And what is the way? Thanks! >-----Original Message----- >Hello! I would like to know is it possible to move the >mails from netscape mail to outlook? And what is the way? >Thanks! >. > <anonymous@discussions.microsoft.com> wrote in message news:d08901c3efb2$759a75e0$a101280a@phx.gbl... > > >-----Original Message----- > >Hello! I would like to know is it possible to move the > >mails from netscape mail to outlook? And what is the way? > >Thanks! ...

Manipulating embeded exccel in word (Repost)
Hi there, I have seen quite a few posts on this and know it is "easily" accomplished, however I have a bit of a different requirement. I have a Word document that I have embedded the following: - 1 Sheet that holds score data that users can edit within word - 6 Graphs (from the same workbook) that report the data in different ways. What I want to happen is that when the data in the embedded worksheet is edited, then all the other graphs update to reflect the edited data or scores. Any ideas ? I have one, which is to cycle through each embedded chart and copy the data scores ...

Word 2007 Headings
At the top of my screen Ihave Home, Insert, Page Layout etc. I have to click on these to open the subsets below them. How do I lock the subsets so that they are visible all of the time? Thanks. On Tue, 26 Jan 2010 17:44:05 -0800, longmere <longmere@discussions.microsoft.com> wrote: >At the top of my screen Ihave Home, Insert, Page Layout etc. I have to click >on these to open the subsets below them. How do I lock the subsets so that >they are visible all of the time? Thanks. There are multiple ways: - Double-click any of the items. - Press Ctrl+F1. - ...

Read Receipts won't move to 'delete bin' after processing
Very simply, my read receipts won't move to 'delete bin' after they are processed. This doesn't seem like a complex process. The tracking option allows me to either turn this rule on or off. What else am I supposed to do? I have receipts all over the place and I end up deleteting them manually. Hy, You have to delete it by yourself. It wont work with a rule. >-----Original Message----- >Very simply, my read receipts won't move to 'delete bin' after they are processed. This doesn't seem like a complex process. The tracking option allows me to either ...

Moving Pages between two publications
How can I move several entire pages from one document to another? Not simply the content of the page, but the format as well as positioning? Select all the elements on one of the pages and Group them together (a little icon will appear at the bottom of the bounding box - click it to lock the group) before you Copy and paste to the other publication. "FrankDe" <FrankDe@discussions.microsoft.com> wrote in message news:B2DF180E-4973-43D3-8278-7EDDF897EA60@microsoft.com... | How can I move several entire pages from one document to another? Not simply | the content of the ...

Counting unique cells (with text) in a filtered list
Hi Is there a simple way to count unique text values in the 'header" of a column where the adjoining column has had the filter switched on? Example A Home B Car B Home C Home C Home D Car If filtered on Home in the second column, should show 3 (ie A,B and C). Thanks in advance Try this array formula** : =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A7,ROW(A2:A7)-ROW(A2),0,1)),MATCH(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1)>0,1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key ...

Move mailbox rights in Exchange 5.5 #2
What is the most restrictive set of rights that would allow an administrator to move mailboxes between 2 servers in the same site? Providing the administrator an admin role on the Servers container, on each server, or on the Private/Public Information Store gives him undesired privileges (like changing default storage limits). Thanks in advance for any assistance. ...

Excel 2007 Run Remote/Server/Network/Shared Macro From Custom Butt
In Excel 2003 this was straightforward: add the add-in regarless of location and assign it to a button. I have tried several things in 2007 without success. I've followed the usual links (including to Rondebruin and the others!), local add-ins running remote add-ins, abused my keyboard, kicked some furniture, etc... Still I haven't found a solution. I'm sure the solution isn't as easy as in 2003 but if anyone knows it I would appreciate some advice/directions. Thanks, SQLServant ...

Formatting numbers as words
I found the link to http://www.mvps.org/access/modules/mdl0001.htm suggested in an earlier question. How do I attach this to a specific field? I have a dollar amount that I need to print in words and I don't know how to accomplish that. Thanks for any help. On Mon, 23 Apr 2007 13:28:02 -0700, kltino wrote: > I found the link to http://www.mvps.org/access/modules/mdl0001.htm suggested > in an earlier question. How do I attach this to a specific field? I have a > dollar amount that I need to print in words and I don't know how to > accomplish that. Thanks for any help...

Does Excel data merge with .mrg documents?
I run a database for my company, and always have a difficult (read - lots of hand typing) time getting my .mrg data doc to merge with Excel. Most times Exel will have all the data in one cell (making it impossible to sort), or not come through at all. Is it just me, or does Excel not work well with this kind of document. ...

Move users from none Business Unit to other
hi, is it possible to move a user from one business unit to other ? Yes it is. However when you move them they will lose their security role so you will need to add a security role to their user record "M. Ceccacci" <anonymous@discussions.microsoft.com> wrote in message news:D9C93DCE-6400-4C1F-BE36-C86832BA9DCD@microsoft.com... > hi, is it possible to move a user from one business unit to other ? should have said!! to do this go to settings, business units, users then open up the users record then click actions - change business unit. once done make sure you reassign...