Excel ‘concatenate’: how to combine FirstName LastName columns into one column – Name

The formula is of the format =CONCATENATE(A1,A2) which will combine the separate names in Cell A1 and Cell B1 into one.


On my version of Excel this format will include a space between the two but you can force one if yours doesn’t, with =CONCATENATE(A1, ” “, B1).

Note that Cell C1 is highlighted (surrounded by a green border). If you have lots of names in columns A and B you can double click on the tiny green square at the bottom right and the formula will cascade all the way down your list, stopping at the last item.

This format will also work =A1 & A2 (or if you need a space it’s =A1 & ” ” & A2)

Beware: do not now delete columns A and B or your newly created column C will disappear as each cell is actually a formula, contingent on the contents of other cells. If you want a text-only version I think the quickest way to do this is to select Column C and copy (Ctrl+C) its contents, then paste temporarily into a text file (eg notepad.exe) which will paste the words you see in the column and not the underlying maths. Then paste from the notepad file back into an empty column and you can delete the other three safely.

Why not just collect people’s names as names rather than FirstName LastName?
Sometimes it’s handy to be able to order a spreadsheet of people’s names by their surname as well as by their first name, so it’s quite handy to have one column for their forename and another for their surname.

Further reading
Microsoft’s help page on the CONCATENATE function

This post is a neatened update of a post originally published on my main blog (I’m gradually transferring the techy posts I’ve published there… here).



Downloading your old Twitter faves, setting up IFTTT to capture new ones

Table of Contents

  1. Capturing old favourites
  2. Capturing new favourites ‘going forwards’
  3. Useful background info

1. Capturing old favourites
To download your already-liked favourites do the following

  1. Log into Twitter
  2. Go to tweetbook.in and authorise it to access your account
  3. Select a time range, choose Favorites and create your PDF e-book of your favourited tweets

If you have as many favourites as I have (3,502 over 7 years, oops) you probably won’t be able to get them all in one go (2012 alone yielded a 134 page PDF!) but you have the option of trying to grab them all at once.


Fig 1. Authorise Tweetbook.in with Twitter


Fig 2. Pick a date range… or leave blank to pick all (it may fail if you have lots)


Fig 3. Once your tweetbook is ready the green ‘Download’ button will appear

The output
Each page of the PDF has only a handful of tweets on it (it’s not very efficient) but the timestamp is hyperlinked so you can search for a tweet (Ctrl+F or Command+F to search within any document) and then find the original on Twitter.

Caution: I don’t know if it will display only public tweets that you’ve followed or, because you’ve logged in, if it can pick up any tweets from locked (private) accounts that you follow. Be aware that if you publishly share the contents you might be sharing tweets that people want kept private.

2. Capturing new favourites ‘going forwards’
You can use an IFTTT recipe so that every time you click favourite / like on a tweet it will be saved in some way of your choosing – for example you might use a Google spreadsheet to capture the tweet, or email it to yourself.

To do this… do this

  1. Log in to Twitter and Google Drive / Gmail*
  2. Visit IFTTT and create an account.
  3. This is an example of a recipe you can use:
    Twitter Likes (Favorites) to Google Spreadsheet (other recipe options available*)
  4. You’ll be taken through the steps of connecting your Google Drive as one ‘channel’ and your Twitter  account as another channel – this allows your Twitter account to save your favourites to a Google Drive spreadsheet directly (you don’t need to set that up, it happens automatically).
  5. Favourite a tweet then go and visit your Google Drive and you’ll find a new spreadsheet created with your favourite in. After 1,000 tweets the system will create a fresh spreadsheet (same name with ‘1’ appended, and so on).

*or Evernote, or some other capturing system, examples here and here


3. Useful background info
Favouriting a tweet does not trap it permanently – if the original is deleted then you do not have a copy of it so ‘post-favouriting-processing’ would be necessary to capture it.

Other ways to capture a tweet include

  • taking a screenshot (it can be helpful to include its address / URL)
  • embedding it in a blog or Storify (in both cases subsequent deletion of the original won’t matter as your copy will remain)
  • use Freezepage to capture a copy of the ‘page’ on which the tweet appears (you need to use the tweet’s own address – you can find this in its timestamp – and remove the S from the httpS bit of the address

I’ve written a short post on ‘forensic’ use of Twitter (where you’re collecting someone’s tweets for legal reasons) but note that I’m not a lawyer so bear that in mind.

Further reading
Capturing web pages (remember a tweet IS a web page as it has its own address!) – Nightingale Collaboration


How to find text quickly on a web page or in a file

There’s a very useful keyboard shortcut that will let you jump straight to a word or phrase (or any sequence of letters or numbers1) in almost any document or file – it’s Ctrl+F2.

If you’ve opened a 200 page PDF and want to find the word WIDGET then use Ctrl+F to bring up a ‘find’ search box and type in the word widget. Pressing ‘enter’ on the keyboard will let you bounce through each of instance of the word in the document. Depending on your set up some PDF readers will also let you see a panel with all instances of WIDGET in it, in the sentence, allowing you to see a bit of context and decide which one to look at first.

Word documents
Ctrl+F again. You can also change all instances of WIDGET into GADGET by using Ctrl+H (Shift + Command + H on a Mac) to bring up the Find & Replace option, type widget in the top box and gadget in the bottom (make a decision on upper or lower case matching) and press replace all to convert all widgets to gadgets. True story, I once did this in a spreadsheet of people’s names and addresses and converted all instances of UK into United Kingdom and then discovered someone’s first name had become LUnited Kingdome instead of Luke. Should have selected just the country column but I could also have avoided that by better case-matching.

Excel spreadsheets
Within the sheet you’re on Ctrl+F will do the job but note the option on the search box (below) that appears which lets you pick whether to search within the current sheet or the whole workbook (ie find your word in a cell in a different tab).

Screen Shot 2015-08-02 at 23.24.17

1 you don’t even have to type the full word, just enough letters to pick out the word you want and exclude those you don’t, for example if searching for widget then ‘widge’ would probably do whereas ‘idget’ would also find ‘fidget’.

2 Ctrl+F = holding down the Control key while the letter F is pressed, for Find. On a Mac it’s the Command key instead of Control. You can also access Find in the Edit menu.

Further reading