Bit of a new feature in the blog. I’ve been meaning to do something about all the various wee tips and tricks that help me manage all my day to day tasks more quickly. Every time I’ve thought about it, I couldn’t quite work out how to structure the post – it’d be mahoosive.
I’ve solved this issue by deciding to split it into a series of mini posts. First up is the saviour of all affiliates…
The Concatenation Feature in Excel
For those of you who do not know, Excel has a feature that allows you to take the content of cells within any row, stick them together in any order, and pop them in a single cell all nice and stuck together. I personally use this feature to create large keyword lists around set search term variations, to create urls, and also for tasks such as creating large lists of links to be pasted into websites.
To help out, I’ve popped some screenshots below to illustrate what I’m talking about!!
OK, so we start with the contents of some adjacent cells we’d like to have all living in a single cell together to form a phrase: -
To make this happen, all I have to do is create a very simple formula in excel in an adjacent cell:-
Popping in =concatenate( will automatically cue Excel to allow you to use your cursor to select which cells you want included. These can be anywhere on your spreadsheet and in any order. Simply use the arrow keys to select your cells, placing a comma between them.
To create the appropriate spaces between words or even to add in additional words and symbols you need to place the following between each field selection:-
You can put just about any text or symbol in between those commas, except “-” which does rather confuse the formula function!
The finished formula looks like this: -
Simply press enter and….
All your cells are magically put together!
At this stage, the formula will still be there if you copy the cell content rather than the information you are looking to compile. To paste the info you want into the spreadsheet, simply copy the contents of the column in which your formula is contained, right click, and select “Paste Special” then “Values”. This will remove the formula and leave you with your key phrase.
And That’s It!!
You can use this great function to create all sorts of lists of just about every kind. For me personally, it is one of my biggest time saving devices in the often repetitive world of affiliate marketing.
November 25th, 2008 at 12:44 pm
Hi Kirsty,
Yes this is very useful. I also saw that seobook.com has a freee toll that will do this for you as well. Will even put it into exact/phrase match for you as well.
I just wanted to ask a generalt question (not sure if you want to answer though).
As regards kw tracking, do you use your own bespoke tracking tool or do you use an off the shelf solution?
many thanks,
Marc
November 25th, 2008 at 1:57 pm
You can also use the & character for concatenation, so instead of:
=concatenate(B1,” “,C1,” “,D1)
you could use:
=B1 & ” ” & C1 & ” ” & D1
November 25th, 2008 at 4:12 pm
Hi Kirsty!
Great post! If you combine this with AdWord’s Editor, you’ve got a sort of a poor affiliate’s SpeedPPC!
November 25th, 2008 at 7:39 pm
This will also work in OpenOffice 3.0, for those who haven’t tried it or use it-give it a go!
This will do pretty much anything that Excel can do and can also run Excel macros now too(just pop Option VBASupport 1 in front of your pasted Excel macro)
I almost forgot- it’s FREE
http://www.openoffice.org/
November 25th, 2008 at 11:37 pm
@Marc – I don’t use any custom tracking, just the networks’ in-house tools.
@Lee – yeah, exactly!
@ Ed & Carl, thanks for adding your own tips they enrich the post. Anyone else with their own info to add, please feel free!!
November 26th, 2008 at 8:39 am
Hi Kirsty,
Thanks. I know you will be using the subid/clickref tracking from the networks, but what I mean is do you use some special tool/system to insert unique subid’s for each kw?
Thanks again,
Marc
November 26th, 2008 at 8:50 am
Hi Marc, Not sure what you mean… do you mean do I create a unique tracking ID for every keyword which is passed into the network system? If so, I do this using the concatenate function also. If that’s not what you mean… my sincere apologies for being a thicko!!
November 26th, 2008 at 2:44 pm
Hi Kirsty,
Yes thats what I mean. Ok I see. Thanks.
I am trying to use a system now that passes through the exact KW that was typed. So even if I am bidding on ‘potato’ (phrase match) but the person types in ‘potato uk now’ then this exact phrase will be passed along through to the network.
Just wondering if i am taking it oo far and getting ahead of myself here.
thanks again.
Marc
November 27th, 2008 at 7:12 am
LOL, well you could be Marc. That said, it would provide you with some fascinating data about exactly what was converting!
December 3rd, 2008 at 4:53 pm
Hi Kirsty,
I wonder if you have used “ASAP Utilities”, an excel plugin that is must for everyone that usually play around with datafeed in excel. It saves hell lot of time and helps people poor with excel formulas.
A must affiliate tool.
Cheers,
Mike..