On one of my projects, I have to process data from lots of sources. One of these provides essentially a report in spreadsheet format. I need to strip out all the extraneous things – empty rows, data split into rows by line feeds – so I end up with one line containing a name and address so I can geocode it. To do this in Excel, I use these steps:
- Copy the column (B) containing the name and address to a new worksheet
- On the new worksheet, select the column just pasted, then click (on the Home tab) Find And Select –> Go To Special –> Blanks, then click OK
- Still on the Home tab, select Delete –> Delete Sheet Rows (the paste command brings lots of empty rows from the original worksheet, because of the report formatting; these commands will select and then delete the empty rows)
- Select the Data tab
- Select Text To Columns –> Delimited –> Next
- Step 2 of the Text To Columns Wizard, de-select Tab, select Other, and type Ctrl-J (control-J) in the box beside Other. You’ll just see a period in the box, but the sample display will show the data split into columns. Click Next and then Finish.
- To combine the address for geocoding, use the Excel formula =CONCATENATE(B2,”,”,C2)
For whatever reason, this is a satisfying exercise, watching Excel rearrange the data into a form that is usable for my needs. Especially the text-to-columns function.
From the October 2019 issue:
In a caption on page 44 of August’s issue (Trafalgar: An Overrated Victory?) we wrote that the “Dutch fleet” was destroyed at the battle of Copenhagen. It should of course have read “Danish fleet”. Thanks to the many readers who pointed out this error.”
Sometimes the stranger you meet on this pilgrim’s journey through life is the person that others see you to be. Self-examination is often not the easiest thing to do. Some recent interactions have sent me down that winding path. For now, it’s expressing itself in a reluctance to post much on social media, and avoidance of some of the people with whom I normally interact. I’m still parsing some things that were said online and in real life. In the case of Facebook, I think abandoning the platform isn’t an option, because of an organization page that I need to maintain. But I think a period of minimization is perhaps called for. I’m pretty sure the world can exist without my opinions for a little while, and I know my head needs to clear a bit while I’m waiting for enlightenment and understanding to come.
So once again I’m sitting here on the weekly conference call for this one project I’m on. This is one of those projects that makes me a firm believer in past lives and karma, and also that in a past life I must have done some bad, bad things. It has been essentially the same call for over a year – the client has the same problems, they’re going to do the same things to address those, they’re always about to do those things, but week after week they haven’t been done. And there’s one person on the call who I call Acid Trip Girl, because it’s like listening to someone on an acid trip. If I took a drink every time she mentions stored procedures I would be laying out dead drunk every Thursday afternoon. It’s a classic case of “why use ten words to explain when I cane use a hundred”. But it isn’t nice to talk about people so I’ll stop.
In springtime the flowers,
Like men standing before the old gods,
Lift their faces up to find their fate
Feeling the warmth of the new sun
Feeling the cool of the new earth
Seeded by fate
This is the way we know ourselves
Dancing in the winds
Dancing to songs that don’t belong to us