Be careful if there are commas in your street addresses (101 Main St, Ste 3) which will throw off the import. I use a variant of Shel’s technique. Changing spaces to commas may screw up your names and addresses. I think you’ll end up with: John,Doe,(413),111-1111,123,First,St,Greenfield,01301 Instead of replacing the paragraph marks with a comma, replace them with a tab (^t in Word Find and Replace). This will create a tab delimited file, .TXT, which eliminates the comma issue. This imports directly into Excel. You may have an issue since there are no blank spaces between addresses. If all the addresses are in Greenfield, you can search for 01301^p and replace it with 01301@#@ as described below. The result is kind of messy. Do you want the address all in one cell, instead of Address, City, Zip in separate cells? You won’t be able to do much with the import if it is all in one cell. Usually, I do something like this: Last paragraph mark in the address (if there is a blank line between addresses): Find: ^p^p Replace: @#@ Paragraphs to tabs (^p to ^t) Find: ^p Replace: ^t @#@ back to paragraphs: Find: @#@ Replace: ^p The @#@ is simply a pattern that usually doesn’t occur in most addresses. ^p and ^t must be lowercase. This gives me a tab delimited file with tabs between fields and a paragraph mark at the end of a record. Save as a text file with a .txt extension. That will import directly into Excel with few problems. John Doe^t(413) 111-1111^t123 First St Greenfield 01301^p If you can get a blank line between addresses, so much the better. Otherwise, you’ll have to do a find and replace on individual zipcodes. Similarly you’ll need to put a paragraph mark before Greenfield to get the street address separate from the City/Zip. Find: “Greenfield” Replace”^tGreenfield” Or otherwise separate the street address from the city-zip: Find: “ St “ Replace: “ St^t” and so on for Road, Lane, Court, etc. The spaces on either side of St keep you from replacing “First” with “FirSt^t”. This works in Word Mac or PC, I believe. Do not use wildcards. You can also use the Text to Columns function in Word to help separate the line. You can also use the LEFT(), MID(), and RIGHT() functions in Excel once you have the text imported. Excel has better tools for this than Word. If you drop the leading 0 in zipcodes, you can fix it with this: you can add an apostrophe to the beginning of a zip (Find 01013 Replace ‘01013) or use =TEXT(cell,”00000”) in Excel to create text with the proper zipcodes. Then Copy and Paste Values to remove the formula, cut and paste over the zipcodes in your text. See ( <https://www.ptraining.com/blog/mail-merge-problem-leading-zeroes-missing-from-zip-codes/> https://www.ptraining.com/blog/mail-merge-problem-leading-zeroes-missing-from-zip-codes/) for more info. Don Lesser Pioneer Training, Inc. 139B Damon Road, Ste 8 Northampton, MA 01060 (413) 387-1040 <mailto:dlesser at ptraining.com> dlesser at ptraining.com <http://www.ptraining.com/> www.ptraining.com From: hidden-discuss-bounces at lists.hidden-tech.net [mailto:hidden-discuss-bounces at lists.hidden-tech.net] On Behalf Of Shel Horowitz Sent: Saturday, October 21, 2017 10:04 PM To: David Greenberg <david.greenberg3 at gmail.com> Cc: Hidden-Tech Tech <hidden-discuss at lists.hidden-tech.net> Subject: Re: [Hidden-tech] Text Manipulation Problem If you want something less techie than Perl, try this, using Word's ability to search-replace non-alphanumeric characters: 1. Pop the text into Word. Save the file under another name in case you mess up. 2. Using search-and-replace, change all instances of 01301 to 01301&^&^ (or any other string that would not show up in the data file) 3. change all (413)<space> to (413)<no space> 4. Change all remaining spaces to commas 5. Change all paragraph returns to commas put ^p into the Find box and make sure the p is lower-case) 6. Change all &^&^ back to ^p 7. Scan the file for any tweaks you need, like changing ,, to just one , 8. Save as text Whole thing should take about three minutes. Shel Horowitz - "The Transformpreneur"(sm) ________________________________________________ Watch (and please share) my TEDx Talk, "Impossible is a Dare: Business for a Better World" <http://www.ted.com/tedx/events/11809> http://www.ted.com/tedx/events/11809 <http://www.ted.com/tedx/events/11809> (move your mouse to "event videos") Contact me to bake in profitability while addressing hunger, poverty, war, and catastrophic climate change Twitter: @shelhorowitz * First business ever to be Green America Gold Certified * Inducted into the National Environmental Hall of Fame <http://goingbeyondsustainability.com/> http://goingbeyondsustainability.com <http://transformpreneur.com/> http://transformpreneur.com mailto: <mailto:shel at greenandprofitable.com> shel at greenandprofitable.com * <tel:413-586-2388> 413-586-2388 Award-winning, best-selling author of 10 books. Latest: Guerrilla Marketing to Heal the World (co-authored with Jay Conrad Levinson) _________________________________________________ On Sat, Oct 21, 2017 at 7:34 AM, David Greenberg <david.greenberg3 at gmail.com <mailto:david.greenberg3 at gmail.com> > wrote: I have a hard copy list of names, addresses and phone numbers. I can scan to PDF and then copy and paste to a text editor (BBEdit) or other file. I then need to manipulate the text so that I end up with a csv file that can be opened by a spreadsheet program. Tools that I have at my disposal include BBEdit (with Grep), a MAMP stack, NeoOffice (Mac version of OpenOffice) and FileMaker. Input looks like this: John Doe (413) 111-1111 123 First St Greenfield 01301 Jane Smith 456 So Main Ln Greenfield 01301 Jane Ann Smith (413) 222-2222 <tel:(413)%20222-2222> 78 Main Ct Greenfield 01301 Note that all addresses will include 'Greenfield 01301' and, if the data includes a phone number, it will start with '(413)'. Output should look like this: John,Doe,(413) 111-1111,123 First St,Greenfield,01301 Jane,Smith,,456 So Main Ln,Greenfield,01301 Jane Ann,Smith,(413) 222-2222 <tel:(413)%20222-2222> ,78 Main Ct,Greenfield,01301 Any suggestions greatly appreciated. Thanks. David _______________________________________________ Hidden-discuss mailing list - home page: http://www.hidden-tech.net Hidden-discuss at lists.hidden-tech.net <mailto:Hidden-discuss at lists.hidden-tech.net> You are receiving this because you are on the Hidden-Tech Discussion list. If you would like to change your list preferences, Go to the Members page on the Hidden Tech Web site. http://www.hidden-tech.net/members -------------- next part -------------- An HTML attachment was scrubbed... URL: http://lists.hidden-tech.net/pipermail/hidden-discuss/attachments/20171022/626c5481/attachment.html