Displaying Text - CONCATENATE & TEXT

Excel serves many purposes. One is certainly to perform mathematical operations on your data using any of the hundreds of functions available. If you're just looking to find out the result of one number multiplied by another and are basically using Excel as a high-powered calculator, all you care about is the result. But if you're using Excel for another one of its purposes, that being to arrange data for presentation purposes, you're at some point going to want to format your cells/data. 

Excel, of course, has just about every imaginable way of formatting your cells. You can color them in, wrap them in a border, change the font, and even change the way the data is presented. You can format it as a currency, a date, a number (to any number of digits), a percent, or even a custom format. 

But these options have an important property: they do not change the actual data within the cell...only the appearance of the data. This is almost always a good thing. For example, a large corporation may display a balance sheet rounded to the nearest thousand, but they're actual data certainly shouldn't be rounded. 

The image below shows how an actual number in the function line is carried to four digits while the formatted display in cell A1 in only carried to two digits. The function line shows the true value.

There are times, however, when you do want the actual data to change. In this post, we going to talk through some of these scenarios and a couple of functions that I find to be very useful when directly manipulating data within cells. 

CONCATENATE

Put simply, the CONCATENATE function simply puts the contents of whatever cells you want together. 

If cell A1 contains the text "The quick brown fox" and cell A2 has "jumps over the lazy dog", you can use "=CONCATENATE(A1," ",A2") to get "The quick brown fox jumps over the lazy dog". Notice here that we passed three values to the CONCATENATE function. We had to pass a space (" ") because there was no space either after the word "dog" in A1 or before the word "jumps" in A2. 

There are a number of uses for the CONCATENATE function. Let's say you had a list of names with the last name and first name in separate columns. You could use the CONCATENATE function to put these two together. 

Another scenario would be if you wanted a cell that displays the average cost of a sandwich in a fast food restaurant. You would total the cost of every sandwich option on the menu and divide it by the variety count. In addition to the calculation, you want to label the cell to say "Average = $XX.XX". You could do the following:

	A	B	C	D	E
1	Scenario	Total Cost	Count	Average Cost	Formula
2	Formatted	$84.58	14	$6.04	=B2/C2
3	Concatenated	$84.58	14	Average = 6.04242857142857	=CONCATENATE("Average = ",B3/C3)

Notice that the cells in column D are dynamic (meaning that they will change if you change the data in columns A or B) and that D3 has a description preceding the value. But while D2 contains a formatted number (as currency), D3 contains only text...and it isn't quite right. You'll need to make one additional adjustment to format the value, which we'll describe now with the TEXT function. 

TEXT

The text function takes two parameters: a value you want to format and the format itself. It can take some time to familiarize yourself with the way Excel handles numbers and text. But if I could sum it up in one rule: just about every value in Excel is either a number or text. You can perform mathematical operations on numbers but not on text. You can really only concatenate text. 

Text is text but numbers can be expressed in a variety of ways. Numbers in Excel can be displayed as percentages, currency, dates, scientific notation, and more. But as described before, changing the formatting of a number only changes its appearance. It does not actually change its value. 

If you want to change its actual value or to force the appearance of a number in a particular format, you can convert it to text using the TEXT function.

So, back to our previous example. We have calculated the average cost of a sandwich but Excel has it displayed as a number with a bunch of decimal points. This is ugly because it is unformatted. We want to express it as a currency, with two decimal points and a dollar sign.

You'll most likely want to memorize the formats you're going to use more but the one we're wanting in this case is "$#0.00". The "$" forces it to display a dollar sign before the value while the zeros and the decimal point tell it to put the appropriate number with two numbers to the left and two to the right of the decimal point. 

Our final formula becomes this: "=CONCATENATE("Average = ",TEXT(A2/A1,"$#0.00")).

Here are some other common ones:

	A	B	C
1	Format	Example	Method
2	Date	1/1/15	"m/d/yy"
3	Date	01/01/2015	"mm/dd/yyyy"
4	Date	January 01, 2015	"mmmm dd, yyyy"
5	Number	6.0414	"0.####"
6	Number	03.1050	"00.0000"
7	Percent	85.00%	"00.00%"

There is a lot more to discuss regarding formatting of cells (and we'll go into them in depth in a later post) but the ones above are rather simple. For now, just know that the difference between a "#" sign and a "0" in the number formats is that a "0" forces there to be a digit displayed (even if it is a zero) whereas the "#" allows Excel to not display zeros when it generally violates convention (as it does with leading zeros).