This post will guide you how to use Excel TEXT function with syntax and examples in Microsoft excel.

Description

The Excel TEXT function converts a numeric value into text string with a specified format.

The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.

The TEXT function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the TEXT function is as below:

= TEXT (value, Format code)

Where the TEXT function arguments are:
number -This is a required argument. The value you want to format.
Format code– This is a required argument. The format code that you want to apply.

The “Format Code” can be used in the excel Text function are shown in the below table.

Format Code	Description	Examples
0	only display digits in its place #.00 – Forces the function to display two decimal places	=Text(34.234,”$##.00″) result: $34.23
#	Display the placeholder	=Text(4.527,”#.##) result: 4.53
.	the position of Decimal Point	=Text(342.2,”0.00″) result: 342.20
d	Day of the month or day of week d- one or two digit number (1-31) dd- two digit number (01-31) ddd-abbreviated day of week (Mon to Sun) dddd-full name of day of week(Monday to Sunnday)	=Text(TODAY(),”DDDD”) result: Monday
m	The Month of the Year m- one or two digit number mm-two digit number mmm-abbreviated month(Jan to Dec) mmmm-full name of month(January to December))	=Text(TODAY(),”MM/DD/YY”) result:11/06/17
y	year yy-two digit representation of year(e.g.01,17) yyyy-four digit representation of year(e.g. 2001,2017)	=Text(TODAY(),”MM/DD/YY”) result:11/06/17
h	Hour h-one or two digit number (e.g. 1,23) hh-two digit number (e.g. 01,23)	=Text(14:16,”hh:mm”) result: 14:16
m	Minute m-one or two digit representation (e.g. 1,59) mm-two digit representation (e.g. 01,59)	=Text(14:16,”hh:mm”) result: 14:16
s	Second s-one or two digit representation (e.g. 1,59) ss=two digit representation (e.g. 01,59)

Important Notes:

• The Text formula converts a numeric value to formatted text, but its result can not be used for calculation purpose in the other excel functions or formula.
• the “format code must be marked in the quotation marks. or it will return “#NAME?” error.
• the asterisk character cannot be used in format code.

The below examples will show you how to use Excel TEXT function to convert a numeric value into a text string with specified format code.

#1 To convert a numeric value(34293) with MM/DD/YY format code in B1 cell, just using formula:= TEXT(34293,”MM/DD/YY”).

Excel TEXT Formula Examples

• Excel Convert numbers to Text
  The Text function will accept a numeric value as the first argument, then based on the format code in the second argument to convert the number to text. You can convert all the standard number formats such as: dates, times, currency to Text string in excel.…
• Convert date to month name with Text Function
  If you want to convert the date to a month name, you can use the TEXT function with a specified format code like “mmm”in excel.  You can try to run the following TEXT formula:=TEXT(B1,”mmm”)
• Convert Date to text with Text Function in Excel
  you can use TEXT function in excel to convert dates to text in a specific format code. For example, The TEXT function can use the following patterns, like as: “mm/dd/yyyy”, “yyyy/mm/dd”, etc.
• Convert date to month and year only in excel
  If you want to convert the date to month and year only, you can use “yyyymm” format code within the TEXT function in excel, so you can write down the below TEXT formula:=TEXT(date,”yyyymm”)
• Convert date to month and day only in excel
  If you want to convert the date (mm/dd/yyyy) to month and day only, you can use “mm dd” format code within the TEXT function in excel, so you can write down the below TEXT formula: =TEXT(date,”mm dd”)
• Convert Date to Day of Week in Excel
  If you want to get the day name from a date in excel, you can use the TEXT function with a specified format code like “ddd” or “dddd”. We can try to use the below TEXT formula in excel:=TEXT(date,”ddd”)…
• Sorting IP Address
  Assuming that you have an IP list of data in the range of cells B1:B5, and you want to sort those IP list from low to high, how to achieve the result with excel formula. You need to create a formula based on the Text Function, the LEFT function, the FIND function and the MID function, the RIGHT function..…
• Convert Month Name to Number
  If you want to convert month name to number with an excel formula, or you want to convert 3 letter month name to numbers, you can create a formual based on the MONTH function and the DATEVALUE function…
• Combining Date and Time into One Cell
  To combine the date in one cell and time in another cell in Excel, you can use the TEXT function in combination with the concatenation operator to create a new formula….
• Changing Date Format
  To change the date format as your need in Excel, you can use the Format cell feature. Or You can also use the TEXT function to create a formula to change the date format for the date value in cell in Excel. ….

Frequently Asked Questions

Question1: I have a excel date in my worksheet as: 11/13/2017, and I want to convert this date as: Monday November,12,2017. I don’t know how to achieve this using Text function in excel.

Answer: The format code we can use as: “dddd mmmm,dd,yyyy” to reflect the above request, so we can write down the below Text function with the format code as the second argument.

=TEXT(B1,"dddd mmmm,dd,yyyy")

 

Question 2: I have a numeric value in Cell B1 and want to get the nearest whole number, but I don’t know what format code can be used in TEXT function. anyone can help? tanks

Answer: If you want to display as text string without any decimal places, then you can use format code “0” in the Text function. try to use the following TEXT formula:

=TEXT(1261.23,0)

The returned value will be a text string as: “1261”.

 

Question 3: I have a date value (13-Nov-2017) and want to get the day name of the week and how to conver it using TEXT function in excel 2013?

Answer: To get the day name of the week in excel, you can try to use the format code “dddd” within TEXT function, so you can use the following TEXT formula:

=TEXT("13-Nov-2017","dddd")

 

Question 4:  I have a products table and want to display two decimal places for the value of total amount in the column C.

A                  B                   C

Quantity  unit price    Total Amount

10              $8.42

9                $23.11

Answer: you need use two format code as : “0” and “#”. so we can wirte down the folowing TEXT functon as:

=TEXT(A2*B2,"$#,###.00")

 

Question 5: I want to convert the date to “d mmmm, yyyy” format, how to create a new Text function in excel 2016?

Answer: you can inert the below Text function in one cell to get the expected results:

=TEXT(B1,"d mmmm,yyyy)