Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a word document into an excel spreadsheet
The database that I am working from sends me a report as a word document. I
would prefer that this information be presented in excel, that way it is easier to manipulate the report. There is no option for me to choose between word or excel. I would just like to know how I can transfer data on a word document to an excel spreadsheet? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a word document into an excel spreadsheet
If each record of your WORD Document occupies just 1 line with a paragraph return at the end of each line and you have a "fixed" length for each field then you can copy and paste directly into EXCEL and then "split the text into the applicable number of fields in EXCEL. To see if your "text" lines up into equally spaced fields - apply the COURIER font. Select your report and then paste it into Column A. Once the report is in Excel. Select the column usually column A - then select DATA - Text To Columns and follow the instruction on the popup menu to split the text into individual columns. If you can have your database generate a report as "CSV" or "TABBED" records, then you can import directly into EXCEL. If your database generates a report as a WORD table - then copy and paste the table directly into EXCEL. But for the information to lineup correctly you need to remove all of the following: Any paragraph marks, tab marks and manual line breaks and replace them with a single space. To do this - in WORD select find and replace - select the MORE button - then select Special and then in the select paragraph marks, tab marks and manual line breaks (one at a time) and then in the replace box - just hit the space bar 1 time and the above items will be replaced. For anymore information info on the conversion - I would need to see what the report looks like. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=498035 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a word document into an excel spreadsheet
Thank you for the information it really helped. One more thing is there a way
for me to split the cells all at once instead of having to do it on cell at a time? "wjohnson" wrote: If each record of your WORD Document occupies just 1 line with a paragraph return at the end of each line and you have a "fixed" length for each field then you can copy and paste directly into EXCEL and then "split the text into the applicable number of fields in EXCEL. To see if your "text" lines up into equally spaced fields - apply the COURIER font. Select your report and then paste it into Column A. Once the report is in Excel. Select the column usually column A - then select DATA - Text To Columns and follow the instruction on the popup menu to split the text into individual columns. If you can have your database generate a report as "CSV" or "TABBED" records, then you can import directly into EXCEL. If your database generates a report as a WORD table - then copy and paste the table directly into EXCEL. But for the information to lineup correctly you need to remove all of the following: Any paragraph marks, tab marks and manual line breaks and replace them with a single space. To do this - in WORD select find and replace - select the MORE button - then select Special and then in the select paragraph marks, tab marks and manual line breaks (one at a time) and then in the replace box - just hit the space bar 1 time and the above items will be replaced. For anymore information info on the conversion - I would need to see what the report looks like. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=498035 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a word document into an excel spreadsheet
After you paste the info into EXCEL, Select ALL of Column A or whatever Column you pasted the text into (do not select Cell A1 and DRAG down) and select DATA - then Text To Columns you can manaully split (i.e. Column A) into as many pieces as you want - just keep scrolling to the right and placeing the "tabs" (ie division points) where ever you want. Single click to "place a "cell break"" double click to remove it. If this doesn't answer your question - then let me know. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=498035 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a word document into an excel spreadsheet
Hi I am trying to do a similar thing but when I try to remove the paragraph
returns in my word document the find and replace function does not find any of the paragraph marks (even though they are there). I have tried it with different special characters and this feature doesn't seem to work at all. Is there somethign I am doing wrong or something else I can try? "wjohnson" wrote: If each record of your WORD Document occupies just 1 line with a paragraph return at the end of each line and you have a "fixed" length for each field then you can copy and paste directly into EXCEL and then "split the text into the applicable number of fields in EXCEL. To see if your "text" lines up into equally spaced fields - apply the COURIER font. Select your report and then paste it into Column A. Once the report is in Excel. Select the column usually column A - then select DATA - Text To Columns and follow the instruction on the popup menu to split the text into individual columns. If you can have your database generate a report as "CSV" or "TABBED" records, then you can import directly into EXCEL. If your database generates a report as a WORD table - then copy and paste the table directly into EXCEL. But for the information to lineup correctly you need to remove all of the following: Any paragraph marks, tab marks and manual line breaks and replace them with a single space. To do this - in WORD select find and replace - select the MORE button - then select Special and then in the select paragraph marks, tab marks and manual line breaks (one at a time) and then in the replace box - just hit the space bar 1 time and the above items will be replaced. For anymore information info on the conversion - I would need to see what the report looks like. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=498035 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a word document into an excel spreadsheet
Saved from a previous post:
If your cells in your word table contain paragraph mark or linebreak characters, then excel will bring them over as separate cells. One way around it is to convert those paragraph marks & linebreaks to unique characters, then copy|paste and then convert them back to linefeeds. I like this technique (inside a copy of the word file): Select your table. Edit|replace|Special (show More if required) Find what: (paragraph mark under Special button) replace with: $$$$$ (if $$$$$ doesn't appear in the table) replace all Same thing with Manual Line break (from under Special). Now copy the table into Excel. Edit|Replace Replace what: $$$$$ Replace with: ctrl-j replace all. Don't forget to close the word document without saving (or hit undo as many times as necessary). gartner4121 wrote: Hi I am trying to do a similar thing but when I try to remove the paragraph returns in my word document the find and replace function does not find any of the paragraph marks (even though they are there). I have tried it with different special characters and this feature doesn't seem to work at all. Is there somethign I am doing wrong or something else I can try? "wjohnson" wrote: If each record of your WORD Document occupies just 1 line with a paragraph return at the end of each line and you have a "fixed" length for each field then you can copy and paste directly into EXCEL and then "split the text into the applicable number of fields in EXCEL. To see if your "text" lines up into equally spaced fields - apply the COURIER font. Select your report and then paste it into Column A. Once the report is in Excel. Select the column usually column A - then select DATA - Text To Columns and follow the instruction on the popup menu to split the text into individual columns. If you can have your database generate a report as "CSV" or "TABBED" records, then you can import directly into EXCEL. If your database generates a report as a WORD table - then copy and paste the table directly into EXCEL. But for the information to lineup correctly you need to remove all of the following: Any paragraph marks, tab marks and manual line breaks and replace them with a single space. To do this - in WORD select find and replace - select the MORE button - then select Special and then in the select paragraph marks, tab marks and manual line breaks (one at a time) and then in the replace box - just hit the space bar 1 time and the above items will be replaced. For anymore information info on the conversion - I would need to see what the report looks like. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=498035 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a word document into an excel spreadsheet
Will this work with a txt file opened in word? I'm using Windows xp and
office 2003 on my work computer and for some reason when I use find and replace - special character paragraph mark, it doesn't recognise the paragraph marks in the document..... I tried it on my home computer which is running the beta version of the latest word 2007 and it works ok. I'm wondering if I have a setting wrong on my work computer since the same procedure is not working there. Any suggestions??? "Dave Peterson" wrote: Saved from a previous post: If your cells in your word table contain paragraph mark or linebreak characters, then excel will bring them over as separate cells. One way around it is to convert those paragraph marks & linebreaks to unique characters, then copy|paste and then convert them back to linefeeds. I like this technique (inside a copy of the word file): Select your table. Edit|replace|Special (show More if required) Find what: (paragraph mark under Special button) replace with: $$$$$ (if $$$$$ doesn't appear in the table) replace all Same thing with Manual Line break (from under Special). Now copy the table into Excel. Edit|Replace Replace what: $$$$$ Replace with: ctrl-j replace all. Don't forget to close the word document without saving (or hit undo as many times as necessary). gartner4121 wrote: Hi I am trying to do a similar thing but when I try to remove the paragraph returns in my word document the find and replace function does not find any of the paragraph marks (even though they are there). I have tried it with different special characters and this feature doesn't seem to work at all. Is there somethign I am doing wrong or something else I can try? "wjohnson" wrote: If each record of your WORD Document occupies just 1 line with a paragraph return at the end of each line and you have a "fixed" length for each field then you can copy and paste directly into EXCEL and then "split the text into the applicable number of fields in EXCEL. To see if your "text" lines up into equally spaced fields - apply the COURIER font. Select your report and then paste it into Column A. Once the report is in Excel. Select the column usually column A - then select DATA - Text To Columns and follow the instruction on the popup menu to split the text into individual columns. If you can have your database generate a report as "CSV" or "TABBED" records, then you can import directly into EXCEL. If your database generates a report as a WORD table - then copy and paste the table directly into EXCEL. But for the information to lineup correctly you need to remove all of the following: Any paragraph marks, tab marks and manual line breaks and replace them with a single space. To do this - in WORD select find and replace - select the MORE button - then select Special and then in the select paragraph marks, tab marks and manual line breaks (one at a time) and then in the replace box - just hit the space bar 1 time and the above items will be replaced. For anymore information info on the conversion - I would need to see what the report looks like. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=498035 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing a word document into an excel spreadsheet
If it's a text file that's brought into word, it'll depend on what you do with
it. If you create a table in MSWord, it'll work nicely. If you don't create a table in MSWord, then you may find that what you paste into Excel won't be what you want (everything going into a single cell????). Depends on what you do in MSWord. gartner4121 wrote: Will this work with a txt file opened in word? I'm using Windows xp and office 2003 on my work computer and for some reason when I use find and replace - special character paragraph mark, it doesn't recognise the paragraph marks in the document..... I tried it on my home computer which is running the beta version of the latest word 2007 and it works ok. I'm wondering if I have a setting wrong on my work computer since the same procedure is not working there. Any suggestions??? "Dave Peterson" wrote: Saved from a previous post: If your cells in your word table contain paragraph mark or linebreak characters, then excel will bring them over as separate cells. One way around it is to convert those paragraph marks & linebreaks to unique characters, then copy|paste and then convert them back to linefeeds. I like this technique (inside a copy of the word file): Select your table. Edit|replace|Special (show More if required) Find what: (paragraph mark under Special button) replace with: $$$$$ (if $$$$$ doesn't appear in the table) replace all Same thing with Manual Line break (from under Special). Now copy the table into Excel. Edit|Replace Replace what: $$$$$ Replace with: ctrl-j replace all. Don't forget to close the word document without saving (or hit undo as many times as necessary). gartner4121 wrote: Hi I am trying to do a similar thing but when I try to remove the paragraph returns in my word document the find and replace function does not find any of the paragraph marks (even though they are there). I have tried it with different special characters and this feature doesn't seem to work at all. Is there somethign I am doing wrong or something else I can try? "wjohnson" wrote: If each record of your WORD Document occupies just 1 line with a paragraph return at the end of each line and you have a "fixed" length for each field then you can copy and paste directly into EXCEL and then "split the text into the applicable number of fields in EXCEL. To see if your "text" lines up into equally spaced fields - apply the COURIER font. Select your report and then paste it into Column A. Once the report is in Excel. Select the column usually column A - then select DATA - Text To Columns and follow the instruction on the popup menu to split the text into individual columns. If you can have your database generate a report as "CSV" or "TABBED" records, then you can import directly into EXCEL. If your database generates a report as a WORD table - then copy and paste the table directly into EXCEL. But for the information to lineup correctly you need to remove all of the following: Any paragraph marks, tab marks and manual line breaks and replace them with a single space. To do this - in WORD select find and replace - select the MORE button - then select Special and then in the select paragraph marks, tab marks and manual line breaks (one at a time) and then in the replace box - just hit the space bar 1 time and the above items will be replaced. For anymore information info on the conversion - I would need to see what the report looks like. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=498035 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in an excel macro can you import data from word into a cell? | Excel Discussion (Misc queries) | |||
Cannot open excel inside word document | Excel Discussion (Misc queries) | |||
Problems printing a word document with linked excel tables. | Excel Discussion (Misc queries) | |||
How do I import an excel spreadsheet to a word document | Excel Discussion (Misc queries) | |||
Is it possible to link an Excel spreadsheet to a Word document an. | Excel Discussion (Misc queries) |