How to join text with numbers (video)

Abstract

In this video we look at how to use the TEXT function. The TEXT function is useful when you need to CONCATENATE a number with text using a specific number format.

Transcript

In this video we'll look at how to convert numbers to text using a function called TEXT. This is useful when you want to convert a number to text, or when you want to join a number with text using a specific number format.

Let's take a look.

The TEXT function has just one purpose—to convert a number into a text value using a specific number format. The function takes two arguments: the value or number, and something called "format_text" which is the number formatting you want to use when the number is converted.

In the first table, we have some random numbers in the left column.

I'll add a formula in column C to show just the raw values in the General number format.

In column E, there is already another formula that points back to column B. In this case, however, the same numbers have been formatted with the number formats shown in column D.

The key thing to remember here is that the values in column E are identical to the values in columns B and C. The only difference is the number formatting. You can see this clearly if I temporarily switch to the General number format.

Now I'll use the TEXT function to convert the numbers to text, using the number formats shown in column D. I just need to plug in the value and supply a number format in double quotes.

Notice that the result in F7 is no longer a number; it's actually a text string equal to "January" as we can see by the left alignment.

For the next example, because the number format is already in the table, I can just point back to that value in column D inside the TEXT function to get the number format.

Now I can copy the formula down and you can see that in each case we get a number converted to text.

Now let's join some text with numbers.

In the bottom table, we have another group of numbers and some text that we'd like to join with those numbers. In column E, I'll enter a formula that concatenates the two values together without any formatting.

You can see that the result is not really usable. The numbers without formatting just don't look right. To fix this problem, I can use the TEXT function. This gives us a much better result.

Note that there's no need to expose the number format on the worksheet when you use the TEXT function. I'm just doing it, in this case, to make the formulas easier to enter and also to make it easier to change the number format after the TEXT function has been entered.