Thursday, 29 September 2016

Excelling file names

On a recent trip to South Australia, I acquired 319 new digital items to be filed and named. Many of these are scanned photos, images captured from newspapers, photocopied documents, and photographed family memorabilia. What a bonus for a budding family historian to have acquired so much in a short period but how will I process and organise all this information? On looking at my file naming practice I spotted many errors had crept into my system over the past three years. This time, I was determined to stay consistent by using Excel to generate file names.

Spreadsheets have a function useful for this purpose: Concatenate. This simply means that all the information entered into a series of cells will be combined together into one cell. Why use this for file naming? The column headings on the spreadsheet prompt me to enter the different types of information; names, dates, places, and events. Basically, it is a guide to answering questions about files.
  • Who is it about?
  • When did it happen or when was it generated?
  • Where did it take place?
  • What is it about?
So my spreadsheet column headings reflect the type of information I wish to record. Any cell can be left blank if the type of information in that column is not relevant to the file to be named.
In the column where I want the file name compiled, in this case, I3, I added the formula =CONCATENATE(A3,B3,C3,D3,E3,F3,G3,H3) to join together or combine the information from the cells in columns A to H. This creates my completed file name.

Next , I used the fill handle, (drag down from the bottom right-hand corner of cell I3) to copy the formula down into all the cells in column I. Now any information I add in new rows will automatically be compiled in Column I.

I’ve added an underscore in column E to separate the numbers where there are two dates, simply for ease of reading. I’ve used fill down so that I don’t need to type that underscore every time.

If spaces are required between names one could add two double quotes around a space, into the formula. So to get Horgan John rather than HorganJohn I would need to amend part of the formula (A3,B3) to (A3,“ ”,B3)

The second tab on my spreadsheet replicates the formula for photographs, not all of which are related to family history pursuits. The reminder headings may be interpreted differently as the name column may be used to describe the contents of the photo. By keeping the sheets separate I will be able to sort the data in different ways.

I’ve added a Column J which contains a link to the file on my computer. This link is inserted once the file has been renamed using this system.  Now I need to get back to sorting and naming all those files.

This post on GFC Learn Free explains the concatenate function. There are many excellent Excel tutorials on YouTube. Google Sheets or Open Office users will find this function can be replicated in those spreadsheet programs.

This post first appeared on

No comments:

Post a Comment