DEV Community

loading...

How to Convert Numbers to Text Format in Excel? Easy Tutorial!!

excelgeek profile image Andrews Originally published at geekexcel.com on ・3 min read

Assuming that you want to display leading zero in numbers in the cell, then you need to convert them into Text based on the specified Format text. This post will guide you on how to convert numbers to text with the Excel TEXT function. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Text Function:

  • The TEXT Function will accept a numeric value as the first argument, then convert it into the text based on the format code in the second argument.
  • In Simple words, the text function converts a number to text in the given format.
  • Using this function you can convert all the standard number formats such as date, times, currency to a text string in Excel.

The Syntax of the TEXT function is,

=TEXT(value, format_text)

Steps to Convert Number to Text:

Example 1:

  • If you want to convert the numbers in the cell to Text without any formatting, then you can use the below formula.

=TEXT(B2,”0″)

  • First you need to give the input number and apply the given formula.

Enter a formula
Enter a formula

  • It will convert the number to text with no special formatting. Refer to the below image.

Result
Example 1

Example 2:

  • When you need to display the leading zero in numbers means, you can use the below formula.

=TEXT(B1,”0000″)

  • The formatted text “0000” will display the text string as follows:

Example 2
Example 2

Example 3:

  • In case you want to convert the number into a text string that keeps two decimal digits, just use the below formula.

=TEXT(B1,”00.00″)

  • This formatted text will display the number as a decimal digit values as per the below example image.

Example 3
Example 3

Excel Format Codes with Description:

The below-given table will help to know more format codes in Excel formatting. Make use of it.

| Format

Code

Description Examples
0 It will display the digits in their place. It can force the function to display at least two decimal places. =TEXT(18.3012, “$##.00”)

Output = $18.30

|
| # | This will display the placeholder. |

=TEXT(5.678, “#.##”)

Output = 5.66

|
| . | It shows the position of the decimal point. | =TEXT(45.4, “0.00”)

Output = 45.40

|
| d | Represents the day of the month or day of the week. | =TEXT(TODAY(), “dddd”)

Output = Monday

|
| m | It refers to the month of the year. | =TEXT(TODAY(), “MM/DD/YY”)

Output = 01/04/21

|
| y | Year | =TEXT(TODAY(), “MM/DD/YY”)

Output = 01/04/21

|
| h | Hour is represented by one or two-digit number. | =TEXT(15:20, “hh:mm”)

Output = 15:20

|
| m | Minute | =TEXT(14:15, “hh:mm”)

Output = 14:15

|
| s | Second | Nil |

Conclusion:

In this short article, you can easily learn the instructions to convert numbers to text with the Excel using TEXT Function. If you have any queries/suggestions, kindly drop it in the below comment box. Thanks for visiting Geek Excel!! Keep Learning!!

Read Ahead:

Discussion (0)

pic
Editor guide