[Hidden-tech] Text Manipulation Problem

Don Lesser dlesser at ptraining.com
Sun Oct 22 09:28:38 EDT 2017


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 


Google

More information about the Hidden-discuss mailing list