 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Word / June 2007A question about defining fields in a text list of records
Hey all, I'm a Word novice, and I have a question for an advanced user. I've got a document filled with text that is layed out in the following way: Name: John Smith Phone Number: 555-1212 Address: 1234 Main Street, NY, NY Name: John Jones Phone Number: 555-2000 Address: 4567 Main Street, NY, NY etc., etc. I would like to convert this text to a table with columns labeled Name, Address, Phone Number, etc. and a row for each individual, for eventual import into Excel. Is there a quick way to do this, or perhaps a macro or some such that I could use? Any help would be greatly appreciated! Thanks, Phil Hello Phil, If your data is consistent (e.g. no blank where, say, a phone number should be) you can simply select the text then choose Table menu => Convert => Convert text to table, then specify the number of columns etc. Then format the table. In other contexts, I pre-format my tables to exactly what I want, then make them into an AutoText item. So, for example, if I want a 6-column table with only horizontal borders visible and text of a certain size, with specific leading, I key in a 3-character code then insert as AutoText -- voila! the table appears. If that's of interest (and any of the other techniques in there), take a look at page 126 of some notes on the way I use Word for the Mac, titled "Bend Word to Your Will", which are available as a free download from the Word MVPs' website (http://word.mvps.org/Mac/Bend/BendWordToYourWill.html). [Note: "Bend Word to your will" is designed to be used electronically and most subjects are self-contained dictionary-style entries. If you decide to read more widely than the item I've referred to, it's important to read the front end of the document -- especially pages 3 and 5 -- so you can select some Word settings that will allow you to use the document effectively.] Cheers, Clive Huggan Canberra, Australia (My time zone is 5-11 hours different from North America and Europe, so my follow-on responses to those regions can be delayed) ============================================================ * SUGGESTION -- KEEP REVISITING AFTER YOU POST: If you post a question, keep re-visiting the newsgroup for several days after the first response comes in. Sometimes it takes a few responses before the best or complete solution is provided; sometimes you'll be asked for further information. Good tips about getting the best out of posting are at http://word.mvps.org/Mac/AccessNewsgroups.html and http://word.mvps.org/FindHelp/Posting.htm (if you use Safari you may see a blank page and have to hit the circular arrow icon -- "Reload the current page" -- two or more times). ============================================================ On 27/6/07 8:48 AM, in article 1182898083.571458.259690@k79g2000hse.googlegroups.com, > Hey all, > [quoted text clipped - 21 lines] > > Phil Hi Phil - Like Clive says, the key is consistency in how the content is currently stored. Don't worry about a macro... The process will take only a minute or two regardless of how many records. Automation is only worthwhile if you need to do this repeatedly. If your specimens are accurate I'd further suggest the following before converting the list to a table: 1- Find & Replace for each ³label², such as Find What: Name:{space} Replace With: leave blank, 2- Find & Replace ,{space} with ^p (to split the street,city & state) 3- Find & Replace ^p^p with ^p (to get rid of the "empty paragraphs) 4- Apple+A to select all, Table>Convert>Text to Table: Specify Number of Columns: 5, Select ³Autofit to Contents², Separate Text At: Paragraphs 5- Save As whatever you want to call it, but specify Text Only in the Format list when you save. I would also not worry about captions or formatting - that can all be added once the data is imported to Excel. Note: In Steps 1 & 2, {space} represents just the press of the spacebar - don't type the brackets or the word. Also, the number of columns (5) is based on the sample data. If there are more fields (such as zip code) you can adjust the number accordingly. Also, there are some relatively simple ways to get the first-last names, street-city-state separated into separate fields (columns) once you get the list into Excel. HTH |:>) Bob Jones [MVP] Office:Mac On 6/26/07 6:48 PM, in article 1182898083.571458.259690@k79g2000hse.googlegroups.com, > Hey all, > [quoted text clipped - 21 lines] > > Phil > Hi Phil - > [quoted text clipped - 60 lines] > > > Phil Hey guys, thanks for the help! I think the problem now is missing fields. The data set is not as clean as I'd like, and what I need to do now is insert fields that don't necessarily have anything in them. For example: Name: John Smith Phone Number: 555-1212 Address: 1234 Main Street, NY, NY Fax Number: 555-1000 Name: John Jones Phone Number: 555-2000 Address: 4567 Main Street, NY, NY Fax Number: <blank> The second entry has no fax number, but I need the field in order to make the table columns line up properly. Is there a way to automatically insert fields? For example, if I know that "Fax Number" always comes after "Address", is there a script or something that can say, "Check line below Address - if Fax Number, leave alone, if something else, add Fax Number." I'm simplifying of course, but maybe there's a way? Thanks again, Phil Then you'll just have to add another step to the list - call it 0, because it would best be done *before* step #1: Do a Find & Replace for: Find What: fax number:^p (if there's a space following the : in your data include it - I didn't use one here.) Replace With: fax number:X^p (where X represents any placeholder character or string you want to use - perhaps even the word NONE) This will add "filler" to the records that don't have Fax numbers & won't change the records that do. (Once you get the list into Excel you can easily remove the placeholder data.) You'll also have to include Fax Number: in step #1. Regards |:>) Bob Jones [MVP] Office:Mac > Hey guys, thanks for the help! I think the problem now is missing > fields. The data set is not as clean as I'd like, and what I need to [quoted text clipped - 22 lines] > > Phil On 6/27/07 4:22 PM, in article 1182975745.751094.82060@u2g2000hsc.googlegroups.com, >> Hi Phil - >> [quoted text clipped - 60 lines] >> >>> Phil > Then you'll just have to add another step to the list - call it 0, because > it would best be done *before* step #1: [quoted text clipped - 112 lines] > > >>> Phil Ah, excellent. Now what if the field itself is missing, ie. no label for Fax Number in the record at all? Is there a way to insert that, or do a find and replace for that? Thanks again, you're saving my life here! Phil In that case, let's assume that "Name:" will always be there and always be the first field. Replace that with ^pFiller: ^pFiller: ^pFiller: ^p ^pName: (I showed them vertically, but they're all one line in the Replace With box.) The provides three filler fields and a blank cell at the end of each row. Then convert your table. The fully populated records will have three blank fields hanging out to the right: simply delete those columns to get rid of them all in a single operation. Records with missing fields will have one or more filler fields. In some rare cases where their fields are out of order, you will have to drag the cells into the correct position. But you will get a fully-populated and regular table structure which you can then work with. Cheers On 28/6/07 9:00 AM, in article 1182985201.520023.281100@m36g2000hse.googlegroups.com, >> Then you'll just have to add another step to the list - call it 0, because >> it would best be done *before* step #1: [quoted text clipped - 120 lines] > > Phil  SignatureDon't wait for your answer, click here: http://www.word.mvps.org/ Please reply in the group. Please do NOT email me unless I ask you to. John McGhie, Consultant Technical Writer McGhie Information Engineering Pty Ltd http://jgmcghie.fastmail.com.au/ Sydney, Australia. S33°53'34.20 E151°14'54.50 +61 4 1209 1410, mailto:john@mcghie.name An alternative to John's suggestion - clever as the codger may be :) - I'd just deal with untangling what you've got for now and wait until I got the list into Excel to add any additional fields that aren't already a part of the data structure. Once there it's nothing more than adding captions to columns in he list of records. Regards |:>) Bob Jones [MVP] Office:Mac On 6/27/07 7:00 PM, in article 1182985201.520023.281100@m36g2000hse.googlegroups.com, >> Then you'll just have to add another step to the list - call it 0, because >> it would best be done *before* step #1: [quoted text clipped - 120 lines] > > Phil |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.