16 July 2012

address tricks, part 1

Imagine that I have a text document of addresses in blocks.  Instead, I want a spreadsheet with five columns:  Name, Street Address, City, State, and Zip Code.  If I  have only three addresses, I can retype them.  But what if I  have three hundred?  Or three thousand?

The address lines are separated by a paragraph break (ordinary ‘enter’) and the address blocks by two paragraph breaks.  First, I replace every set of two paragraph breaks with a unique placeholder such as an asterisk (find ^p^p and replace with *).  Now the only paragraph breaks are between address lines.  I replace them with a tab (find ^p and replace with ^t).  Then I return the paragraph breaks between addresses (find * and replace with ^p).  I select all the addresses, copy them, and paste them into the first cell of a spreadsheet.

It now looks like this:

I type the following functions into cells D1 through F1:

D1:   =LEFT(C1,FIND(",",C1)-1)
E1:   =MID(C1,FIND(",",C1)+2,2)
F1:  =RIGHT(C1,LEN(C1)-FIND(" ",C1,FIND(",",C1)+2))

What does this mean?  The functions LEFT, MID, and RIGHT return text strings from those positions in a cell.  Their arguments are the cell in which to look for the text string (here, C1); for MID only, the starting character; and for all three, the number of characters to return.  The functions FIND and LEN return numbers.  FIND, not surprisingly, finds the position of a character or text string in a cell; its arguments are the thing to be found, the cell in which to look (again C1), and, optionally, the character in that cell at which to start looking.  LEN returns the total number of characters in a cell, including spaces; its only argument is the cell whose characters to count.  The functions above use FIND and LEN within LEFT, MID, and RIGHT to indicate starting and ending places in C1.

I highlight all three cells (D1 to F1) and drag them down to the bottom of the list; then I copy the new material and, selecting ‘Values’ under ‘Paste Special’, paste it back into the same area.  Now it no longer depends on column C, and I can delete that.  The final results:

What if I also want the name separated into two columns, for Surname and Given Name?  For the answer to that question, look for a Part 2.

No comments:

Post a Comment

Because this blog is now archived, it is unlikely that I will allow new comments to be posted. You are welcome to contact me through the form on the 'About' page.