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

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

concatenate

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).

 

 

How to remove the audio track from a video made on an iPhone, using iMovie

Sometimes I take a video of something and on listening to it hear other noise or conversation that I don’t particularly want to include. Here’s how I get rid of it in ‘post-production’.

This may not be the best way of doing it and I’m certain it’s not the only way. However, it works and doesn’t involve downloading any extra apps or spending money, so it’ll be the method I’m likely to favour. If you know of a better way, others might be interested so please feel free to share your improvements and suggestions in the comments below.

This post assumes that you have an iPhone with the built-in camera, and iMovie apps. I’m running iOS 7.6 (if you’ve got a higher iOS then my screenshots might look a little different from yours, but hopefully not so much as to make the instructions unworkable).

Here are the basic instructions, repeated below with the addition of screenshots

1. Record your video
2. Open iMovie
3. Click the + at bottom right to create new project
4. Click Movie
5. Click Create Movie
6. Tap the icon to ‘insert media’
7. Select your chosen video, it goes yellow
8. Click the down arrow to insert
9. Your video appears as a panel of images, and the cursor leaps to the end
10. Click the video panel, it goes yellow
11. Audio is already selected, click the two dots below the dustbin, click detach
12. The audio track is now separated from the video and you can, if you wish, move it into a different position. It’s already selected (yellow) so press delete to remove it
13. Try out your now silent video, all the sound should have gone
14. To export it click on the back arrow at the top, then the upload icon for sharing options. Your options will depend on your phone and apps but I’ve got Facebook, YouTube, Vimeo etc. and if I scroll along there’s the email option at the end and WhatsApp. The save video, in the lower down (not brightly coloured) panel will save it to your camera roll, from which you can also email and send it other places etc.).

To share it on Twitter, note that it will need to be reduced in length to just a few seconds and you’ll need to save this ‘iMovie project’ to your camera roll to do so (and ensure that you’ve granted Twitter access to your iPhone’s camera roll [at some point I might add something about how to do this but it’s in Settings somewhere]), you can also email it to yourself.

You’ll end up with a much smaller, squarer version of your video, rather than the rectangular one you created. I’ve no idea why, nor how to make it larger. If you know a better way that will let you end up with a video that’s identical to the one you recorded minus the audio, please let me know. I’m less interested in costly professional tools though.

The same again but with added images

1. Record your video
2. Open iMovie (it was already installed on my phone)
3. Click the + at bottom right to create new project

photo 1(5)
4. Click Movie (in blue, below)

photo 2(4)
5. Click Create Movie (top right in pale blue, below)

photo 3(3)
6. Tap the sprocketed film + musical note icon at the top to insert media

photo 4
7. Select your chosen video, it goes yellow – you can slide the yellow boundaries to make your clip shorter too.
8. Click the down arrow to insert

photo 1(5)
9. Your video appears as a panel of images, and the cursor leaps to the end

photo 2(4)
10. Click the video panel, it goes yellow

photo 3(3)
11. Audio is already selected, click the two dots below the dustbin, click Detach

photo 4
12. The audio track is now separated from the video and you can, if you wish, move it into a different position. It’s already selected (yellow) so press the dustbin icon to delete it

photo 1(5)

13. Try out your now silent video, all the sound should have gone
14. To export it click on the back arrow at the top, then the upload icon for sharing options. Your options will depend on your phone and apps but I’ve got Facebook, YouTube, Vimeo etc. and if I scroll along there’s the email option at the end and WhatsApp. The save video, in the lower down (not brightly coloured) panel will save it to your camera roll, from which you can also email and send it other places etc.).

photo 2(4)

Above: Click the left-facing arrow in the top left to start the export process.

Below: you’ll see this screen, choose the upload icon in the middle to upload your silent film.

photo 3(3)

Below: Mail is at the end of the list of things you can send the file to and you can also save it to your camera roll.

photo 4

 

Converting a single A4 PDF into two A5 copies on one page

Solved with PDFescape which is a free PDF editing tool for Windows, I used the online version. (I think on a Mac you’d just use the Preview tool which lets you edit PDFs anyway).

The situation I was in with a one-page A4 PDF was A and I wanted to get two copies of it so that it printed out like B. I was missing a sort of duplication step which I thought I could fix in the File / Print stage, but apparently not.

aversusbpdfs

1. Go to http://www.pdfescape.com/windows/ and click on the free online bit on the left, it looks like this

freeonlinepdfwrangling.png

2. Choose how to get your PDF into PDFescape – I used the second option ‘Upload PDF to PDFescape’, clicked ‘browse’ to select though you can also drag and drop, and waited for it to load.

3. Click on Page in the menu on the left, then Append and upload a second copy of the same PDF*

bitontheleft

*Can be a different one of course if you’re trying to add different PDFs together

4. Now you’ll have two pages, like this – click the little green button with white chevrons on it (see pic below) to download a copy.

boom

5. To print two onto one sheet open the PDF and it’s File / Print then the options should appear

Page Sizing & Handling
Multiple
Pages per sheet = 2

printing.png

Google spreadsheets timestamp – US to UK date format settings

If you use Google Forms the chances are that your data will go to a Google Spreadsheet, which you can view at your leisure. Each time someone fills in the form a new record (row) is created in the spreadsheet and the time they did it is added in the timestamp column with the date.

Google defaults to American settings so will generally show US date format (month/day/year).

If you want to make it UK date format (day/month/year) do the following: Open the form, click on File, then Spreadsheet settings… then change ‘United States’ to ‘United Kingdom’ and click the blue Save settings button. The spreadsheet will refresh and the timestamps will now be UK style.

1. File / Spreadsheet settings…

Screen Shot 2016-03-06 at 23.30.50

2. Locale – change United States to United Kingdom, click Save settings.

Screen Shot 2016-03-06 at 23.31.08

3. What the setting looks like with United Kingdom (I got carried away taking screenshots).

Screen Shot 2016-03-06 at 23.31.24

 

 

How to attach an open file to an email on a Mac h/t @davidious

Well this has been mildly revolutionary for me, having been unable to do this for the last three years that I’ve owned a Mac – I now know how to attach an open file to an email directly without going up and down through file hierarchies to find it. There’s some background below on why I always thought this was easier on a PC but today I learned where I’ve been going wrong.

I bet this is one of those things that everyone else knows but me 😉

How to attach an open (saved) file directly to a new email message
Arrange your windows so that the New Email and File windows are visible together. On the file you’re working on there’ll be an icon in the middle of the title bar, eg it will look like this for Word  Screen Shot 2015-08-29 at 11.19.36 and you can just drag the blue icon with ‘a’ on it into your email attachment area. I’ve tried this successfully in Thunderbird and Gmail (remember that you must have saved the file somewhere before you try and drag / drop it or it won’t work).

Hooray! Thanks to @davidious for telling me this.

On a PC
If you’ve a file open (eg if you’re working on it) that you’ve saved then you can get hold of its location by starting the File / Save As… process. A file manager / windows explorer window will open showing you where your file is located in its folder hierarchy. You can copy that address and when you want to attach an email in your programme can paste it into the address bar of the new instance of the file manager that opens. It’s a work of seconds. Couldn’t be easier. Since I’d never found a satisfactory way to replicate this on a Mac (even after asking several Mac using friends and colleagues and on Twitter) I assumed it wasn’t possible. My sad efforts at finding a Mac-based solution are here on my other blog.

Obviously now I will have to try this icon thing on the PC too as that’ll speed things up there as well. It will also be the first time I’ve thought that doing something on a Mac is simpler than on a PC 😉

Possibly there will be some dreadful spam advertisement below, sorry about that.