How to use TEXT function in Excel spreadsheets [Tip]

Excel’s TEXT function is one that you can convert, or combine, numbers and text with. You can also add it to spreadsheets to convert numbers to alternative formats. The basic syntax for this function is TEXT(value, format).

For example, you could convert a date such as 12/07/2014 to numerous text formats with this function. First, right-click a cell, select Format Cells and click Date to choose a date format for the cell. Then enter 12/07/2014 in the cell as below.


With the TEXT function we’re going to convert the numeric date format to a text alternative. Click a cell to add the function to, and press the fx button. Select the TEXT function to open the window in shot below.


Click the button beside Value and select the cell you entered the date in. In the Format_text box inputdddd, mmmm dd yyyyas below. Then press OK to close the window.


The TEXT function cell will now include the date Saturday, July 12 2014 as below. So it’s converted 12/07/2014 to its text alternative. You can also convert the date to various other text formats such as July or Jul by entering formats such as “mmmm,” “dddd” or “mmm” in the Format_text field.


You can also combine cell values with text. For example, enter Jack in cell A3 and 25 in A4. Then click another cell, and enter =A3&” carried “&TEXT(A4, “00”)&” units” in the formulae bar. When you press Enter, the function cell will match the one below.


You can adjust the formulae above by entering alternative text and cell references. Note that the “00” in the formulae specifies the number format. So you can switch that to various alternative formats.

So with this function you can convert and combine numeric values with text. This YouTube video also shows how you can add the Excel TEXT function to your spreadsheets.

Related Posts