Excel TEXT Function

The Excel TEXT function returns a numeric value as text, in a specified format

Example: Excel TEXT Function

Excel TEXT Function

METHOD 1. Excel TEXT Function using hardcoded values

EXCEL

=TEXT(B5,"dd/mm/yyyy")
Result in cell D5 (15/03/2017) - returns the value 42809 in "dd/mm/yyyy" text format.

=TEXT(B6,"$0.00")
Result in cell D6 ($500.00) - returns the value 500 in "$0.00" text format.

=TEXT(B7,"0.00")
Result in cell D7 (500.00) - returns the value 500 in "0.00" text format.

=TEXT(B8,"0.00%")
Result in cell D8 (80.00%) - returns the value 500 in "0.00%" text format.

METHOD 2. Excel TEXT Function using links

EXCEL

=TEXT(B5,C5)
Result in cell D5 (15/03/2017) - returns the value in cell (C5), which is 42809, in "dd/mm/yyyy" text format.

=TEXT(B6,C6)
Result in cell D6 ($500.00) - returns the value in cell (C6), which is 500, in "$0.00" text format.

=TEXT(B7,C7)
Result in cell D7 (500.00) - returns the value in cell (C7), which is 500, in "0.00" text format.

=TEXT(B8,"0.00%")
Result in cell D8 (0.00%) - returns the value in cell (C5), which is 500, in "0.00%" text format.

METHOD 3. Excel TEXT function using the Excel built-in function library with hardcoded values

EXCEL

Formulas tab > Function Library group > Text > TEXT > populate the input boxes

=TEXT(B5,"dd/mm/yyyy")
Note: in this example we are converting the value in cell (B5), which is 42809, to a dd/mm/yyyy text format. 		Built-in Excel TEXT Function using hardocded values

METHOD 4. Excel TEXT function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > Text > TEXT > populate the input boxes

=TEXT(B5,C5)
Note: in this example we are converting the value in cell (B5), which is 42809, to a text format specified in cell (C5), which is dd/mm/yyyy. 		Built-in Excel TEXT Function using links

METHOD 1. Excel TEXT function using VBA with hardcoded values

VBA

Sub Excel_TEXT_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("TEXT")

'apply the Excel TEXT function
ws.Range("D5") = Application.WorksheetFunction.Text(ws.Range("B5"), "dd/mm/yyyy")
ws.Range("D6") = Application.WorksheetFunction.Text(ws.Range("B6"), "'$0.00")
ws.Range("D7") = Application.WorksheetFunction.Text(ws.Range("B7"), "'0.00")
ws.Range("D8") = Application.WorksheetFunction.Text(ws.Range("B8"), "'0.00%")

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named TEXT.
Value to Format: Ensure that the value that you want to format is captured in range ("B5:B8").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5"), ("D6"), ("D7") and ("D8") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Value to Format: Select the value that you want to format by changing the range ("B5:B8") to any range in the worksheet, that doesn't conflict with the formula.

METHOD 2. Excel TEXT function using VBA with links

VBA

Sub Excel_TEXT_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("TEXT")

'apply the Excel TEXT function
ws.Range("D5") = Application.WorksheetFunction.Text(ws.Range("B5"), ws.Range("C5"))
ws.Range("D6") = Application.WorksheetFunction.Text(ws.Range("B6"), "'" & ws.Range("C6"))
ws.Range("D7") = Application.WorksheetFunction.Text(ws.Range("B7"), "'" & ws.Range("C7"))
ws.Range("D8") = Application.WorksheetFunction.Text(ws.Range("B8"), "'" & ws.Range("C8"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named TEXT.
Value to Format: Ensure that the value that you want to format is captured in range ("B5:B8").
Format: Ensure that the format corresponding to the values is captured in range ("C5:C8").

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5"), ("D6"), ("D7") and ("D8") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Value to Format: Select the value that you want to format by changing the range ("B5:B8") to any range in the worksheet, that doesn't conflict with the formula.
Format: Select the format by changing format values in range ("C5:C8") to any format that is within Excel's capability.

Usage of the Excel TEXT function and formula syntax

EXPLANATION

DESCRIPTION
The Excel TEXT function returns a numeric value as text, in a specified format.
SYNTAX
=TEXT(value, format_text)
ARGUMENTS
value: (Required) A numeric value to to convert to a specified text format.
format_text: (Required) The format that will be applied against the selected value.

ADDITIONAL NOTES
Note 1: Using Excel the format_text argument needs to be inserted inside double quotation marks..
Note 2: You can select an existing format from the Formal Cells box or create a custom format.