TEXT Function

Description

TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols.

Syntax

TEXT(value, format_text)

Parameters

value Required. A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
format_text Required. A numeric format as a text string enclosed in quotation marks, for example "m/d/yyyy" or "#,##0.00". See the following sections for specific formatting guidelines.

Examples

The example may be easier to understand if you copy A1:C14 to a blank worksheet.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
A B C
Data
2014-12-24 13:35:05
41997
1234
Excel
Formula Result Description
=TEXT(A2,"yyyy-mm-dd") 2014-12-24 Display year, month, and day.
=MOD(TEXT(A2,"[m]"),60) 35 Display minutes.
=TEXT(A2,"[s] \S") 3628589705 S Displays elapsed time in seconds..
=TEXT(A3,"[$-409]dd-mmm-yy") 24-Dec-14 Display year, month, and day.
=TEXT(A3,"#,##0") 41,997 Display a thousands separator.
=TEXT(A4,REPT("0p, ",LEN(A4)-1)&"0p") 1p, 2p, 3p, 4p Special usage.
=TEXT(A4,"[>100]!"&RIGHT(A4)) 4 Special usage.
=TEXT(FIND("x",A5&"x"),"[>"&LEN(A5)&"]!0") 2 Special usage.

Video training

See also: Excel text functions

ASC
Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
BAHTTEXT
Converts a number to text, using the ß (baht) currency format
CHAR
Returns the character specified by the code number
CLEAN
Removes all nonprintable characters from text
CODE
Returns a numeric code for the first character in a text string
CONCATENATE
Joins several text items into one text item
DOLLAR
Converts a number to text, using the $ (dollar) currency format
EXACT
Checks to see if two text values are identical
FIND, FINDB
Finds one text value within another (case-sensitive)
FIXED
Formats a number as text with a fixed number of decimals
WIDECHAR
Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
LEFT, LEFTB
Returns the leftmost characters from a text value
LEN, LENB
Returns the number of characters in a text string
LOWER
Converts text to lowercase
MID, MIDB
Returns a specific number of characters from a text string starting at the position you specify
PHONETIC
Extracts the phonetic (furigana) characters from a text string
PROPER
Capitalizes the first letter in each word of a text value
REPLACE, REPLACEB
Replaces characters within text
REPT
Repeats text a given number of times
RIGHT, RIGHTB
Returns the rightmost characters from a text value
SEARCH, SEARCHB
Finds one text value within another (not case-sensitive)
SUBSTITUTE
Substitutes new text for old text in a text string
T
Converts its arguments to text
TEXT
Formats a number and converts it to text
TRIM
Removes spaces from text
UPPER
Converts text to uppercase
VALUE
Converts a text argument to a number

Leave a Reply

Your email address will not be published. Required fields are marked *