Mix text with numeric data in the same Excel cell

In Excel, sometimes we need to display both text and values in the same cell. You can use the following formula techniques to mix text and numbers in the same cell.

One of the tips

For example, if the A6 cell contains a value of 1234, we can enter the following formula in another cell, such as D5:

= "Total:" &a6

The D5 cell will display: "Total: 1234", as shown in Figure 1.

Figure 1 Calculation results

In this case, the symbol & works by connecting the text "Total" to the contents of the A6 cell. Applying a numeric format to such a cell that contains a formula does not work because the cell contains text instead of a numeric value.

The second skill

If you use the text function cleverly in a formula, you can also implement text and values in a single cell.

For example, we could enter the following formula in another cell, such as D6:

= "Total:" &text (A6, "$#,# #0.00")

This will appear as: "Total: $1,234.00" in D6, as shown in Figure 2.

Figure 2 Calculation results

Skills of the third

Here is an example of using the now function to implement the same cell and display both text and date-time values.

= "This report is printed on" &text (Now (), "yyyy-mm-d h:mm am/pm")

The input is completed as follows: "This report is printed on 2006-04-19 4:21 PM", as shown in Figure 3.

Figure 3 Effect using the NOW function