Search Not Just Numbers

Tuesday 13 November 2012

EXCEL TIP: Using data in sentences

Microsoft Office users will tend to use Excel to handle numbers and data, and Word to handle text - which, of course, is how it should be.

Although, I know many accountants who would use Excel to write letters if they could - and many Office users who are not au fait with the workings of spreadsheets that will stretch Word's data handling capabilities well beyond what is decent!

There are, however, legitimate reasons for using text in Excel and some very useful functions that make it work well.

In particular, I am thinking of those times where we want to write sentences that use data.

Some examples are:
  • Spreadsheet headings that might include a date, or a department name;
  • Notes to accounts that might refer to actual money values within a sentence.
I am sure you can think of many more.

Excel has a really simple way of handling this. and at the heart of it is the ampersand character (&).

The ampersand character can be used to join any pieces of text together in a formula. This text can be actual text (included in quotation marks), or references to text.

So, for example:

="My spreadsheet"&" won't work" returns My spreadsheet won't work

or more usefully:

="Balance Sheet as at 31st March "&A1 returns Balance Sheet as at 31st March 2012 if cell A1 contains the current year (2012)

="The Creditors balance of £"&A1&" includes £"&A2&" due to Group Companies" returns The Creditors balance of £35623 includes £5261 due to Group Companies - where cells A1 and A2 contain those numbers

You can even force the format of the data by using the TEXT function. In the example above, replacing A1 with TEXT(A1,"#,##0") will format it as 35,623. The TEXT function obeys all of the standard rules for formats - including dates, etc.

Now you can stop all that fiddling around with the text elements of your reports and have Excel populate that too!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

14 comments:

  1. Yes,Glen, I have some examples of how EXCEL can "speak spanish" :)
    You can see examples at: http://empleoyformacion.jccm.es/otras-secciones/observatorio-empleo/informes-epa-castilla-la-mancha/
    All reports are completely maked with EXCEL.
    I hope you love it...
    Isidro.

    ReplyDelete
  2. The spoken contents are maked with individual sentences originated by formulas like:
    =CONCATENAR("Respecto al mismo trimestre del año anterior, el número de personas activas ha ";SI(REDONDEAR('5'!F48;1)=0;CONCATENAR("permanecido constante en ";TEXTO(REDONDEAR('5'!C48;1)*1000;"#.#0");" personas");CONCATENAR(SI(REDONDEAR('5'!F48;1)>0;"crecido";"disminuido");" un ";TEXTO(ABS('5'!G48);"#.#0,00%");", lo que supone la ";SI(REDONDEAR('5'!F48;1)>0;CONCATENAR("incorporación de ";TEXTO(REDONDEAR('5'!F48;1)*1000;"#.#0");" personas al");CONCATENAR("salida de ";TEXTO(-REDONDEAR('5'!F48;1)*1000;"#.#0");" personas del"));" mercado de trabajo regional"));". La media nacional registra en el mismo periodo un ";SI(REDONDEAR('5'!F40;1)=0;CONCATENAR("total de ";TEXTO(REDONDEAR('5'!C40;1)*1000;"#.#0");" activos, permaneciendo el mismo valor que en el ";nombre_anterior);CONCATENAR(SI(REDONDEAR('5'!F40;1)>0;"aumento";"descenso");" interanual ";SI(REDONDEAR('5'!G48;4)=REDONDEAR('5'!G40;4);"idéntico al de Castilla-La Mancha";CONCATENAR("del ";TEXTO(ABS('5'!G40);"#.#0,00%")))));".")

    ReplyDelete
  3. Isidro

    Some impressive Excel generated reports there. I must admit I've never really understood the benefit of the CONCATENATE function when the "&" character seems to do the same job in a simpler way.

    I suspect it is just preference as I don't think the result is any different.

    ReplyDelete
  4. Thanks for that. I have been using CONCATENATE but have found it clumsy. Once I get the hang of it this will be a lot easier.

    ReplyDelete
  5. Anonymous

    I'm pleased you found it useful.

    ReplyDelete
  6. Simple advice well presented. So often such tips sound so complicated my brain just switches off! Thanks

    ReplyDelete
  7. Glen, thanks for the & tip! I was trained to use only the CONCATENATE function and got well at using it, but I'll give the & tip a try next time and report back on how it goes for me and my Excel reports!

    Glad I found this blog.

    ReplyDelete
  8. Sue

    Pleased you like the blog. Do let me know if you find anything that CONCATENATE does that & doesn't.

    ReplyDelete
  9. I knew about the & (and how many people knew that it is called an "ampersand"?!) but I was not aware of the TEXT features. That solves a problem that I had with dates. Thank you.

    ReplyDelete
  10. Always good to hear of a problem solved. The TEXT feature is very useful for dates.

    ReplyDelete
  11. This is a great tip. I knew about using the & in Access but didnt realize it was also available in Excel. I would use the concatenate function (which can accomplish the same thing), but this is much easier to write the formula and the formuala is easier for someone else to understand.

    ReplyDelete
  12. Thanks for that, I've always used CONCATENATE however from now on i'll use & (much easier to type)! :)

    ReplyDelete
  13. Thanks for the tip it extremely useful and very easy to use

    ReplyDelete
  14. Number formatting within concatenation can be dealt with by either the ROUND or DOLLAR formulae.
    DOLLAR is the currecny format so will give £ and , on thousands.
    SO for example "xxx x x xx x x "&DOLLAR(M20,0) will show the value of M20 with a £ sign, commas on thousands and 0 places.

    ReplyDelete