Using something like CONCATENATE it is possible to combine text and numbers into a cell in Excel to create a sentence. But if you are not careful, the number format will be wrong and it will revert to a general format instead of being a percentage or a formatted number (so lots of decimals, no thousands spacing etc).
Below how to combine text and numbers and keep the number format.
Table of contents
YouTube Keep number format when combining text and numbers
TEXT function allows formatted number in text cells
As shown below (1) if you try and combine text and numbers (cell A16 with cell B16), although the number is formatted in the cell, when it is combined it loses its formatting and reverts to a plain number.
You can improve this by using the TEXT function to create a helper cell (D16) or combine it directly into a CONCATENATE formula. The syntax of the TEXT function is:
TEXT(the cell with the number in, the format you want it to maintain).
So as per (2) below =TEXT(B16, “# ##0”) tells it to make the number in B16 a ‘word’ with the thousands split with a space. Note the inverted commas for the format are critical. The YouTube video shows you how you can find all codes, especially for the more complex ones.
As (3) indicates the combined text and number now looks better.
TEXT function allows percentage numbers in text cells
Similar to the above, you can include a nicely formatted percentage directly in a text cell.
(1) shows what Excel will use if you don’t use the TEXT function. Notice that even though C17 is formatted with no decimals, A13 shows all these decimals.
Using the TEXT function as an in-between step (2), you have =TEXT(C17,”0.0″) so that as per (3) you see the percentage shown within the text but in the format you want.
Include a currency amount in a sentence with the correct format
For currency numbers there is another function that makes it a little easier. Although it is called DOLLAR, it will use the currency of your computer so on mine it shows up as Rands (ZAR).
So below, similar to the TEXT function, (1) shows what the sentence looks like with the unformatted number included in the text combination. (2) uses the DOLLAR formula with the syntax being
=DOLLAR(cell with number, number of decimal places to use).
As shown in (3) the combined text and number looks well formatted with the currency symbol and only the number of decimals you want to show.
Related
Extract text from Excel cell AFTER an instance of a character