Introduction

In this article we will show you how to use the Excel TEXT function which will help you convert numerical values into text. We'll explain what it does and go over some examples step by step.

Prerequisites

• For beginners, understanding Excel could be a difficult start. You must have basic knowledge of the Excel equations for learn this topic quickly. To try the tutorial, you must have Microsoft Excel built right into your device.
• The TEXT() function is a feature that is available Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003 and even lower versions.

Excel TEXT function

Microsoft Excel is mostly about numbers, and most of the time you'll be working with numbers in your worksheets. There are times, when you need to convert numerical values into text or string, and so, in this case, the TEXT() function is useful.

The formula syntax for the TEXT() function is as follows:

TEXT( VAL, FORMAT )

The two arguments means:

Cell Value- This argument refers to a numerical value to be converted into text. It could be a date, number, reference to a cell that holds a numerical value or some other function which returns the number or date.

Text Format - This argument refers to template you wants to apply for. It is included in the quotation marks in the context of a format tag "mm / dd / yy''.

The Text formula can be used in:

• Dates to be shown in a particular format.
• To merge numbers or dates with specific text or characters.
• To view numbers in a more readable manner, either in a format that makes you more understandable in a users.

NOTE: In Excel the TEXT feature converts a numerical value to a text string. In other equations, however, you would not be able to use the output of your Text formula.

Excel TEXT function format codes

As you observed, the TEXT function syntax is very simple, with a tough part including a proper format code that will output the number as you like. The TEXT method generally allows most of the format codes that are used in the Excel number formats.

0 - Digit placeholder which displays irrelevant zeros.
Example:
#.000- it displays three decimal place.
If you're going to type 1.7 in the reference cell then, it shows a 1.700 results.

# This digit placeholder wouldn't display any extra zeros.
Example:
#.### - It displays up to three decimal places.
If you're going to type the 1.7 in the reference cell then, it displays as 1.7, however if you type 1.77777, it would displays as 1.78.

? - It refers to digit placeholder which leaves space for insignificant zeros but do not display them. The column numbers are usually matched at a decimal point.
Example:
#.?? - It displays a maximum of two decimal places in a column and aligns the decimal points.

Comma (,) - Refers to a thousands operator.
Period (.) - Refers to a decimal point.

Excel TEXT function format codes

The section will show you the different format codes used in the TEXT function. The TEXT() function allows you to change the appearance of a number by applying formatting codes to it.

You can also include in the format code some of the following characters and they will be displayed in the same way as entered:

• Plus and minus operators: + and -
• Less than and greater than sign ``
• Open and close parenthesis ()
• Colon :
• Apostrophe '
• Ampersand &
• Tilde ~
• Caret ^
• Space character ()

Excel TEXT function with date

You can use any of the following format codes below when using the TEXT() function with data and time temporal values in Excel:

• d-A day of the month or week
  Example:
  d -one or two numbers without leading into zero (1 to 31).
  dd- two digit numbers that leading into zero (01-31)
  ddd- three-letter abbreviation (Sat to Mon)
  dddd- the day of the week complete name (Saturday to Monday)
• m - A month used as a part of date.
  Example:
  m- one or two numbers without leading into zero (1 to 12)
  mm- two digit numbers with leading a zero (01 to 12)
  mmm- the abbreviation of the month (Dec to Jan)
  mmmm- complete name of the month e.g January to December
• y- It refers to a year.
  Example:
  yy- two digit numbers (07 means 2007)
  yyyy- four digit-numbers (2010, 2011, 2012 etc.)
• h Refers to Hour.
  For example:
  h- one or two numbers without leading a zero (1 to 24).
  hh- two numbers begins leading with a zero (01 to 24).
• m- Refers to a minute.
  Example:
  m- one or two numbers without leading a zero (1 to 60).
  mm- two numbers begins leading with a zero (01 to 60).
• s- Refers to a second
  Example:
  s- one or two numbers without leading a zero (1 to 60).
  ss -two numbers leading with a zero (01 to 60).

To demonstrate the following format codes, let's create an example.
Supposed the original date is 06/14/2010.

This are the result for the following Text function:

= TEXT(A2, "mmm dd yyyy")

Formatted date Jun 14 2010

=TEXT(A2, "d-mmm-yy")

Formatted date 6-Jun-10

=TEXT(A2, "ddd")

Formatted date sunday

=TEXT(A2, "ddd dd mmmm, yyyy")

Formatted date Sunday 14 June, 2010

Conclusion

We hope these examples have given you a greater understanding of the Excel TEXT function. It is a function that comes up extremely often and it's good to know by heart. Thank you for joining us!