Google Sheets TEXT Function

This post will guide you how to use Google Sheets TEXT function with syntax and examples.

Description


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

The TEXT function can be used to convert a number formatted as text according to a specified format in google sheets.  The purpose of this function is to convert a number to text in a number format. and its returned value is a number as text in the given format.

The TEXT function is a build-in function in Google Sheets and it is categorized as a Text Function.

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 TEXT function takes two arguments, value and format_code. Value is the number, date or time to format. and it should be a numeric value. Format_code is a pattern by which to format the number.
  • The output of TEXT function is always a text string. If you want to format a number, just apply a regular number formatting in google sheets.

Google Sheets TEXT Function Examples


The below examples will show you how to use google sheets 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(B1,"MM/DD/YY") //it returns 11/20/93

google sheets text function

 

Related Posts

Add Leading Zeros in Google Sheets
Add Leading Zeros To Numbers in google sheets1

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

Google Sheets EVEN Function
google sheets even function1

This post will guide you how to use Google Sheets EVEN function with syntax and examples. Description The Google Sheets EVEN function rounds a given number up to the nearest even integer. You can use the EVEN function to return ...

Google Sheets COUNTBLANK Function
google sheets countblank function1

This post will guide you how to use Google Sheets COUNTBLANK function with syntax and examples. Description The Google Sheets COUNTBLANK function use to count the number of empty cells in a range of cells. You can use the COUNTBLANK ...

Google Sheets DEGREES Function
google sheets degrees function1

This post will guide you how to use Google Sheets DEGREES function with syntax and examples. Description The Google Sheets DEGREES function converts radians into degrees. And it will return a numeric value. For examples, if you pass the PI() function into ...

Google Sheets COSH Function
google sheets cosh function1

This post will guide you how to use Google Sheets COSH function with syntax and examples. Description The Google Sheets COSH function returns the hyperbolic cosine of a given angle. The purpose of this function is used to get hyperbolic ...

Google Sheets COS Function
google sheets cos function1

This post will guide you how to use Google Sheets COS function with syntax and examples. Description The Google Sheets COS function returns the cosine of a given angle. If you want to supply an angle to COS function in ...

Google Sheets COMBINA Function
google sheets combina function1

This post will guide you how to use Google Sheets COMBINA function with syntax and examples. Description The Google Sheets COMBINA function returns the number of combinations for a given number of items and it includes repetitions. You can use ...

Google Sheets COMBIN Function
google sheets combin function1

This post will guide you how to use Google Sheets COMBIN function with syntax and examples. Description The Google Sheets COMBIN function returns the number of combinations for a given number of items. And you can use the COMBIN function ...

Google Sheets CEILING.PRECISE Function
google sheets ceiling.precise function1

This post will guide you how to use Google Sheets CEILING.PRECISE function with syntax and examples. Description The Google Sheets CEILING.PRECISE function returns a given number rounded up to the nearest multiple of a given number of significance or returns ...

Google Sheets CEILING Function
google sheets ceiling function1

This post will guide you how to use Google Sheets CEILING function with syntax and examples. Description The Google Sheets CEILING function returns a given number rounded up to the nearest multiple of a given number of significance. You can ...

Sidebar