Menu

*TEXT FUNCTION is found under the TEXT FUNCTION CATEGORY of GOOGLE SHEETS.*

*TEXT FUNCTION is specially created to format the values and make them more readable as per our requirement.*

* *

* *

TEXT FUNCTION allows us to format our number into more readable format. There are several standard codes by the use of which we can customize the format for better readability.

Simply saying it is just the way to change the format of the cell just like we change the format using the CHANGE FORMAT DIALOG BOX.

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- A basic understanding of the formats in google sheets will be helpful.
- Of course, Excel software.

**The Syntax for the TEXT function in Excel is**

**=TEXT ( VALUE TO BE FORMATTED , FORMAT CODE WHICH IS TO BE APPLIED )**

**VALUE TO BE FORMATTED **This is the value on which we want to operate and change the format. It can be a number, date or time.

**FORMAT CODE **is the code of the format which is to be applied on the given value.

In simple words, TEXT FUNCTION changes the format of the cells. We can use any format using the Text function and perform various functions such as

- applying leading zeros,
- putting the comma after a fixed number of digits for currency
- extracting date and time from a long date.
- converting the format of date or time
- creating any special phone numbers and many more.

We’ll try them in the example discussed below.

The concept of text function is simple.

As the first argument, we have the value on which we want to operate. As the second argument , we tell the TEXT FUNCTION, in which format we want to see the value.

Read the following statement twice.

THE NUMBERS CAN BE DENOTED BY A 0 , # OR ? FOR TELLING THE TEXT FUNCTION ABOUT THE FORMAT WE WANT.

THE TIME AND DATE RECOGNIZES HH FOR HOURS IN NUMBERS , MM FOR MINUTES [NUMBER ], SS FOR SECONDS [ NUMBER ], MM FOR MONTHS DD FOR DATE AND YY OR YYYY FOR YEARS. [ Exhaustive list is given below ]

`d for the day of the month as a single digit [ 1,2...30,31 ]`

.`dd for the day of the month as double digits [ 01, 02....30, 31 ] .`

`ddd`

for the short name of the day [ Mon, Tue, Wed..] .`dddd`

for the full name of the day of the week. [Monday, Tuesday,….Sunday ].`m`

for the month of the year as a single digit [ 1, 2 ,…..,12]. The same letter ‘m’ is used for minutes also if it is used with hh or ss as time. [ hh is for hours and ss is for seconds ].`mm`

for the month of the year as two digits [ 01, 02, …. 12 ] The same code ‘mm’ is used for minutes also if used with hh or ss as time.`mmm`

for the short name of the month of the year. [ Jan, Feb, Mar….]`mmmm`

for the full name of the month of the year. [ January, February…]`mmmmm`

for the first letter in the month of the year. [ J for JANUARY, F for FEBRUARY and so on ]`yy`

for the year as two digits. [ 19, 20, 21 …]`yyyy`

for the year as four digits. [ 2020, 2021 … ]`HH`

for the hour on a 24-hour clock. [ two digits of the hour 11, 12,…]`hh`

for the hour on a 12-hour clock. [ two digits of the hour 01, 02…]`ss`

for the seconds in a time.`ss.000`

for milliseconds in a time.`AM/PM`

for displaying hours based on a 12-hour clock and showing AM or PM depending on the time of day.

Keeping these points in mind let us try to put this theory into action.

Suppose we have a 9 (Nine) number digit say 1234546789 and we want to put a hyphen (-) at position after 2 digits and 6 digits from the right. Let us create format for this.

We’ll use the formula

=TEXT(123456789, “###-####-##”) or

=TEXT(123456789, “000-0000-00”) or

=TEXT(123456789, “???-????-??”)

All of them would give the result as 123-4567-89

But there is one difference in all the three when it comes to show the significant and non significant digits.

# IGNORES THE NON SIGNIFICANT ZEROES. IF ANY 0 IS AT THE STARTING OR END OF THE NUMBER, IT’LL BE REMOVED.

0 HONORS THE NON SIGNIFICANT ZEROES. IF THE 0 IS AT THE STARTING OR AT THE END OF THE NUMBER [ AFTER THE DECIMAL] IT’LL BE RETAINED.

Similarly , we can try any format. For the help we can use the FORMAT DIALOG BOX TOO. which can be opened easily by CTRL+1 or RIGHT CLICK>FORMAT CELLS.

*Let us try to convert various formats using the TEXT FUNCTION.*

VALUE | PLANNED | RESULT | FORMULA USED |

5463 | CURRENCY WITH $ AND SEPARATOR | $5,463 | =TEXT(F6,”$#,#00″) |

3216546123 | () IN THE FIRST THREE DIGITS | (321)-6546123″) | =TEXT(F7,”(###)-######0″)”) |

6/30/2020 | EXTRACT DATE AND MONTH | 30/06 | =TEXT(F8,”dd/mm”) |

18:30:54 | EXTRACT HRS AND MINUTES | 18:30 | =TEXT(F9,”hh:mm”) |

CURRENT DATE | PUT DATE IN YY/DD/MM FORMAT | 20/28/11 | =TEXT(Today(),”YY/DD/MM”) |

6/30/2020 | DAY | Tuesday | =TEXT(F12,”DDDD”) |

6/30/2020 | MONTH | June | =TEXT(F13,”MMMM”) |

0.321 | PERCENTAGE UPTO 1 DECIMAL | 32.1% | =TEXT(F14,”#.0%”) |

0.321 | PERCENTAGE UPTO 2 DECIMAL | 32.10% | =TEXT(F15,”#.00%”) |

4.25 | FRACTION | 4 1/4 | =TEXT(F16,”# ?/?”) |

17.33 | FRACTION | 52/3 | =TEXT(F17,”?/?”) |

18:53 | TIME WITH AM/PM | 6:53 PM | =TEXT(F18,”H:MM AM/PM”) |

Many examples has been shown above to use the TEXT FUNCTION.

The generalized steps are following.

- Place the cursor where you want to get the output.
- Put the formula =Text(Value, “Format in the way you want the output”).
- The answer would appear.

For the reference , the above examples can be taken for help.

OTHER WAYS TO REACH THIS ARTICLE

## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.