Text Function in Excel : Here, we will discuss about syntax and usage of TEXT function.

Description:

The TEXT Function in Excel is used to convert number into given text format. It returns text value, so it may not perform mathematical task on given result. We can use this formula to covert numeric or date value in a specified format.

Syntax:

=TEXT(value,”Format_Text”)

  • value – Numeric value or cell reference that contain numeric value to convert into text.
  • Format_Text – Type of Text format that we want to apply on value. Format should be within double inverted commas(” “)

When we can use TEXT function in Excel:

  • To display dates in a specific format
  • Print numbers in a specific format
  • To combine Text and Numbers

Example 1:

In this example we will learn to convert weekday number in weekday name.

text function in excel

Note : In the above example the Text Function convert weekday number(1 to 7) into Weekday Name.

Format of the Text Function:

Format Description Example
Format for Date / Time
d Day of Month or Day of Week
  • d – print date(1 to 31)
  • dd – print date in two digit(01 to 31)
  • ddd – weekday like(Sun to Sat)
  • dddd – print weekday like (Sunday to Saturday)
m Month Number or Name (when part of Month)
  • m – print month like(1 to 12)
  • mm – print month in two digit(01 to 12)
  • mmm – Month name like(Jan to Dec)
  • mmmm – print month name like (January to February)
y Year
  • yy – print 2018 as 18 (in two digit)
  • yyyy – print year in four digit(2018,2019)
h Hour
  • h – Hour without leading zero, like(1 to 24)
  • hh – Hour with leading zero, like (01 to 24)
m Minute (when part of Time)
  • m – Minute without leading zero, like(1 to 60)
  • mm – Minute with leading zero, like (01 to 60)
s Second
  • s – Second without leading zero, like(1 to 60)
  • ss – Second with leading zero, like (01 to 60)
AM/PM Print Time in 12-Hour Format, followed by AM or PM.
Format for Numbers
0 Digit placeholder Format : Text(A2,”#.00″)
If we write 3.4 in referenced A2 cell it will print 3.40
# Digit or Zero placeholder Format : Text(A2,”#.##”)
If we write 3.4 in referenced A2 cell it will print 3.4 only and if we write 3.666, it will print 3.67
Dot(.) Decimal Point Format : Text(A2,”##.##”)
If we write 34 in referenced A2 cell it will print 34.00
Comma (,) Thousand Separator Format : Text(A2,”##,###”)
If we write 25000 in referenced A2 cell it will print 25,000
TEXT Function in Excel

You May Also Like

Useful Shortcut Keys in Excel

EXACT Function in Excel

IF Function in Excel

One thought on “TEXT Function in Excel”

  • Mrs Rajeswari Raghavan
    February 17, 2020 at 7:29 am
    Permalink

    Good. Thanks

    Reply

Leave a Reply

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