Looking for an Excel wizard

[Hilary Marsh]

Hi all,

A colleague and I are trying to figure out some advanced Excel tricks for an enormous content audit. The document we received from the client has each level of the URL in a different cell. There are more than 100,000 lines in this spreadsheet, so combining them manually is just not an option.

Does anyone know how to combine cells in a way that will produce actual clickable URLs?

Also, what's the best source for advanced Excel how-tos?

Thanks!

Hilary

Hilary Marsh  |  312-806-7854  |  hil...@hilarymarsh.com

Content strategy for associations, nonprofits, corporations:
websites, blogs, social media, e-newsletters
http://www.hilarymarsh.com 
also hilarymarsh on LinkedIn, Twitter, Facebook, Pinterest, Slideshare, etc.

[John Mohr]

Hi Hilary,

This may be what you are looking for: http://office.microsoft.com/en-us/excel-help/concatenate-HP005209020.aspx

John

--
You received this message because you are subscribed to the Google Groups "Content Strategy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to contentstrate...@googlegroups.com.
To post to this group, send email to content...@googlegroups.com.
Visit this group at http://groups.google.com/group/contentstrategy.
For more options, visit https://groups.google.com/d/optout.

[Hilary Marsh]

Yes, I think it is -- this explanation is clearer than lots of others I saw online.

Thank you, John!

[Tony Chung]

The manual process:

1. Use =concatenate( ) to creates the link.

2. Copy the link column and paste as values.

3. Open each cell for editing and press enter to save it to trigger Excel's auto replace.

Easy way:

Do the first two steps manually, then record the action of Pressing F2 to open a cell, close the cell, and go to the next cell.

Trigger the macro, and monitor it (or not) to stop it when complete.

My way:

Program a macro (not record macro) that did all this and triggered the cell save routine to make it active. Macro would be reusable in other projects.

I learned a few tricks working with Excel since 1993. ;-)

For others, stack exchange, stack overflow, Mr Excel, and one other site whose name I don't remember the name are great. I only use the sites search engines pull up

Let me know if you want some code.

-Tony

[Lisa Trager]

I've often wished I could take an advanced Excel course just to learn more tricks and shortcuts that I could apply to all the spreadsheets I work on.  It could also be a great topic for a CS workshop!  Anyone interested in leading it?  Contact me and I'll see if we can get this on the CSNYC schedule!

Lisa Trager
tragester AT GMAIL

[Tony Chung]

What would you be most interested in learning? I should reconsider my dislike of teaching and spend more time with this.

-Tony

On Tuesday, March 25, 2014, Lisa Trager <trag...@gmail.com> wrote:

I've often wished I could take an advanced Excel course just to learn more tricks and shortcuts that I could apply to all the spreadsheets I work on.  It could also be a great topic for a CS workshop!  Anyone interested in leading it?  Call me and I'll see if we can get this on the CSNYC schedule!

Lisa Trager

[John Tulinsky]

I would like to see a good, general tutorial on writing scripts/macros for Excel. The problems I encounter tend to be similar to the one that started this thread, in fact I've run into that specific one a few times. With a little bit of web search I'm usually able to find a script that does what I need but I'd like to take my understanding beyond the cut-and-paste level to where I can create it myself. I'm sure general tutorials are out there, maybe I should just do a little bit of web searching :)

 

[Tony Chung]

The big problem is that my understanding grew exponentially from record macro to full on coding after I took a course in ASP scripting. I had also done PHP and JavaScript by that time, so programming functions in excel and word developed naturally.

So, from a CS perspective, I can see these ideas we used in a big site becoming a presentation:

- Content matrix for content audit

- Content mapping to new information architecture

- Content export to outlines for writing, or HTML to demonstrate menus in a live wireframe

- Content completion reporting based on topic colours

The needs were defined by our content strategy group, and I built code to generate the output. One of the team found code on the web to test topic colours, and I integrated it into our system.

But this would take all of 15 minutes to demonstrate. Then when I open the code people would freak out. 

I guess then we'd have to discuss source code management, revisions, and proper macro deployment.

-Tony

[Paola Roccuzzo]

>I'd like to take my understanding beyond the cut-and-paste level to where I can create it myself.

But that is called 'reinventing the wheel', and it's considered bad practice in coding ;)

Jokes apart, most of our frustrations stem from the fact that we are abusing a tool which was invented for calculations. We have inherited the bad practice from other disciplines, and Microsoft banked on it--but we should be moving away from the spreadsheet paradigm where possible!

Waiting for that happy day, and having to deal with the umpteenth spreadsheet from hell, I have found a tool called Open Refine (ex Google Refine) an absolute *GODSEND* for dealing with messy datasets, from data cleaning, to semantic clustering--and you can write your own functions (for my spreadsheet the basic ones were more than enough, btw).

http://openrefine.org/

I can wholeheartedly recommend it (also to the guys talking about Linked Data in the other thread!)

P.

[John Tulinsky]

I'm a huge fan of Open Refine! I completely agree that Excel is frequently abused but like it or not we often have to work with giant, ugly spreadsheets. Ideally we would have at our fingertips a toolbox full of things like Open Refine, assorted Excel macros and so on to use as appropriate.

[Efthimios Katsanos]

Hi Hilary,

combine the cell by using the concatenate() function and then use the hyperlink() function to turn it into a clickable link

=HYPERLINK(CONCATENATE(A1;B1;C1;D1))

PS. One of the best advanced excel sites and how-to is http://chandoo.org/wp/

Best 

Efthimios

[mbaker.analecta]

I'm with Paola on this. Excel is essentially an unstructured database, and while that is useful for some purposes, as is unstructured text, it is not something a discipline devoted to structure should be embracing.

Mark

Sent from Samsung tablet

-------- Original message --------
From Paola Roccuzzo <paola.r...@gmail.com>
Date: 03-26-2014 3:18 AM (GMT-05:00)
To content...@googlegroups.com
Subject Re: Looking for an Excel wizard

[Hilary Marsh]

Hi everyone,

It's great to know that so many others have the same questions as I do about how to use this ubiquitous but not necessarily appropriate tool, and that there are experts among us who know the answers too! Thanks for all the great solutions, ideas, and options. 

OpenRefine sounds like a great tool, and I'm looking forward to checking it out. And thanks to the several folks who shared information about the concatenate formula.

OpenRefine aside, I would also be interested in learning more advanced Excel skills, since that's still the primary tool in use at the moment.

Best,

Hilary

[Tony Chung]

I can see a cool talk comparing the use of Excel macros with tools like Open Refine, which appears to be purely web based.

-Tony

[david hendler]

Speaking of web based sheets, I've been using Google Sheets a lot lately. Their "new" Sheets--released earlier this month--have a very tight integration with Google Analytics. Previously, one needed to import a script for every sheet and set permissions every time.

What I like about Google Sheets is that it's available everywhere there is a web connection and browser, and in addition to its tight connection to Analytics, it also integrates well with Google Sites to make nice looking charts available, without having to share all of the data points (and thus confusing your audience).  You can also use nearly every formula you would otherwise use with Excel.

[Paola Roccuzzo]

OpenRefine is browser-based: the application runs locally.

And speaking of, the UI is really old-school open source (I feel euphemisms are due) :)

It takes a lot of clicking around to get familiar with it, and the documentation is... old-school open source, but the pain is worth it!

[Lisa Trager]

Wow - thanks for the heads up about OpenRefine!  This is why I love this group... learn new and applicable things related to what I do all the time!  I agree John and Tony that a ideal workshop/course would be both a "good, general tutorial on writing scripts/macros for Excel" as well as "comparing the use of Excel macros with tools like Open Refine."   Tony - go for it and let me know when you have a presentation together!

Best,

Lisa Trager
TragesterATgmail
www.tragerstrategy.com