It is a common operation that fill a new cell that concatenates a cell with string and a cell with numbers.
For example, you might be asked to show a column of reports that display the sales’s results. Example:
Dobney: $8,500.
The problem is that when you join numbers in a text string, the number formats don’t follow. See the following figure. Note that the numbers in the associated strings do not accept the formatting of the source cells.
To resolve this issue, you must encapsulate the cell reference for the numeric value in the TEXT function. Applying he TEXT function is required for formatting your data. The formula shown in this figure solves the problem:
The TEXT function requires two arguments: value and a valid Excel format. You can apply any format you want for as long as you want in a recognized format in an excel format.
For example, you can specify that this formula to display $99
= TEXT (99.21, ” $## “)
You can type this formula in Excel to show 99.2
= (99.21, ” 0.0)
An easy way to get the formatting of a given number is to using the Format Number dialog box. To display the dialog box and get the syntax, follow these steps:
Select any cell and right click then choose Format Number.
Click the Number format tab and select the format you want.
On the left side of the Format Number dialog box, click Custom.
Copy the syntax in the type field.
