Excel Text Function

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 function example

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)

 

 

Related Posts

How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...

How to Change Date to The Day of Week in Excel
How to Change Date to The Day of Week 6

In excel, we can type a date properly if set cell format as ‘Date’. If there is a list includes multiple dates, is there any way to convert these dates to the days of week accordingly? Actually, there are some ...

How to Create Increment Number with Texts in Excel
Create Increment Number with Texts 11

Sometimes we want to fill 1,2,3,4,5… into each cell in a column, for implement this we can enter 1 in the first cell and then drag fill handle down to fill the following cells. But if there are some texts ...

How to Generate Random Time in Excel
Generate Random Time 7

In some special situations we may need to generate some random times in worksheet. We can enter time manually and one by one, but if we want to require a lot of random dates in excel worksheet, we will spend ...

How to Combine Text and Date into one Cell in Excel
combine text and data into one cell2

This post will guide you how to combine text and dates in the same cell in Excel. How do I combine text and date values into one Cell in Excel 2013/2016. Combine Text and Date into One Cell Assuming that ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel
highlight cell or row if date8

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...

How to Convert Military Time to Standard Time in Excel
convert military time to standard5

This post will guide you how to convert military time to standard time with a formula in Excel. How do I convert from military time 17:29:30 to regular time 5:29:30PM in Excel.  How do I Convert Standard time to military ...

How to Delete or Remove Year from a Date in Excel
delete year from date2

This post will guide you how to delete or remove year from a given standard date in Excel. How do I remove the year from a date with a formula in Excel. Remove Year from a Date Assuming that you ...

How to Convert Date to YYYY-MM-DD format in Excel
convert date format yyyymmdd5

This post will guide you how to convert the current date to a specified date format in Excel. How do I convert date to YYYY-MM-DD format with Format Cells Feature in Excel. How to convert date format to a specific ...

How to Concatenate Cells and keeping Date Format in Excel
concatenate cells and keep date format1

This post will guide you how to concatenate cells and keeping data format in Excel. How do I concatenate cells and retain the certain Date format with a formula in Excel 2010/2013/2016. Concatenate Cells and Keeping Date Format Assuming that ...

Sidebar