CEO needaspreadsheet.com
Columnist
Share this content

EXCEL TIP: Using data in sentences

13th Nov 2012
CEO needaspreadsheet.com
Columnist
Share this content

LettersMicrosoft 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".

Tags:

You might also be interested in

Replies (2)

Please login or register to join the discussion.

avatar
By mikerees
19th Nov 2012 11:40

Enhanced number formatting

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 

Thanks (0)
Glen Feechan
By Glen Feechan
22nd Nov 2012 19:21

DOLLAR works well for that application

Mike

Thanks for the comment.

Yes, DOLLAR works well as an alternative to TEXT for the currency format.

I'd used TEXT in the post due to its wider application.

Thanks (0)