How To Convert Numbers To Text In Excel Using Top 2 Methods

How to Convert Numbers to Text in Excel?

For converting numbers to text in Excel, there are two different methods:

Method #1 – Use the “TEXT” function in ExcelMethod #2 – Use the “Format Cells” option

Now, let us discuss each of the methods with an example.

#1 Convert Numbers to Text using Excel TEXT Function

The TEXT functionTEXT FunctionTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more converts numbers formatted as numbers to text in the specified format in Excel.

TEXT Formula in Excel

Arguments

value: The value for which text formatting is required

format_code: The formatting code is necessary for a number

The value can be any number. It can be entered directly or as a cell reference. It can also be a mathematical operation or a date. You could also specify TODAY() or MONTH() etc. in the value.

There are various format_code available in Excel. The format_code is always given in a double quotation mark. For example, you can use “0” to specify an integer with zero decimal place, “0.000” to specify three decimal places, “0000” to add leading zerosLeading ZerosLeading zeros are zeros that are added to figures without altering their mathematical values. This is done to keep a specific format in a sheet or to prevent errors. We may add Leading Zeros in Excel by utilizing the Right, TEXT, and Concatenate Functions.read more, etc. We can also select percentages, scientific notations, currency, degrees, date formats in the TEXT function, such as “0.00%”, “$0”, “##0° 00′ 00″”, “DD/MM/YYYY.”

Returns

The function returns a number as text in the specified format.

Suppose you have cell B4 containing the number 25.00 and formatted as a number. You want to convert numbers to text format.

You may give the TEXT formula as:

= TEXT (B4, “0”)

It will return the number in text format.

Suppose we have a number as 0.781 in cell B5. We want to convert this number to a percentage.

We can use the following TEXT formula:

= TEXT (B5, “0.0%”)

As a result, it will return 78.1% as a TEXT format.

Suppose we have a number 21,000 in cell B6. We want to display the number as currency in the dollar.

To do this, we can use the TEXT Formula in ExcelTEXT Formula In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more:

= TEXT (B6, “$0”)

It will return $21000.

Suppose we have a date 10/22/2018 in cell B7, and we have to convert the date to another format.

We can use the TEXT formula:

= TEXT (B7, “mmmm dd, yyyy”)

It will return the date specified, i.e., October 22, 2018.

#2 Convert Numbers to Text using Excel Format Cell Option

Besides using the TEXT function, we can also use the format cell option to convert numbers to text in Excel.

Suppose we have a number 25 in cell B11. We want to convert numbers to text.

To do so, we can press “CTRL + 1″(or COMMAND + 1 on Mac). As a result, a dialog box may appear, similar to the one shown below.

It mentions that the cell is currently formatted as a number. Now, we must select the “Text” option in the category.

And click “OK.” We notice that the number has been formatted as “Text” now.

Similarly, we can change the formatting to a percentage, date, scientific notation in excelScientific Notation In ExcelIn Excel, scientific notation is a specific style of writing numbers in scientific and exponential forms. Scientific notation compactly helps display values, allowing us to compare and use the same in calculations.read more, etc.

Suppose we want to convert cell B13 to percentage format.

We must select the cell and press “CTRL + 1” (or COMMAND + 1 for Mac) and select “Percentage.”

Then, click on “OK.”

Instead of pressing “CTRL + 1” (or COMMAND + 1 in Mac), we can also right-click on the selected cell and then click on “Format Cells,” as shown below.

As shown in the above steps, we can select the “Text” option in the category and click “OK.”

Where can you Convert Numbers to Text in Excel?

Now, let us see some examples of converting numbers to text in Excel.

Example #1

Suppose we have the start and end timings of different sessions held in the upcoming workshop organized by your company, as shown below. We are supposed to merge the two times, creating a range (time duration).

We first need to convert the time into “Text” format and then merge them to get a time range. For the first session, we can use the following TEXT formula:

= TEXT( B4, “hh:mm” ) & ( ” – ” ) & TEXT( C4, “hh:mm” ) & ( ” Hrs” )

It will return the time range for the 1st session.

Now, we can drag it to the rest of the cells to get the range for the rest of the cells.

Now, let us see the syntax in detail:

TEXT( B4, “hh:mm” ) & ( ” – ” ) & TEXT( C4, “hh:mm” ) & ( ” Hrs” )

TEXT( B4, “hh:mm”) will convert the given time in cell B4 to text in an hour: minute format (24 hours). Similarly, TEXT( C4, “hh:mm”) will convert the given time in cell C4 to an hour: minute format.

& (“-“) & will add “-” in between the two times, thus, creating a range.

& (“Hrs”) it will add the text Hrs at the end.

Similarly, we can also create a range of dates by modifying this syntax.

Example #2

Suppose we have data for certain transactions accomplished in the past few years. Of those transactions, we want to know how many numbers of transactions were made during a particular month of a specific year and the total transaction amount. Therefore, the transaction data is given the cells B5: C19, as shown below.

The month and year to look for are given in cell E5. To search the number of transactions held in the given month, we can use the SUMPRODUCT formula:

= SUMPRODUCT( –(( ISNUMBER ( FIND ( TEXT (E5, “MMYY”), TEXT ( B5:B19, “MMYY”))))))

And press the “Enter” key.

Now, to identify the total transaction amount during that period, we can use the following SUMPRODUCT FormulaSUMPRODUCT FormulaThe SUMPRODUCT excel function multiplies the numbers of two or more arrays and sums up the resulting products.read more:

=SUMPRODUCT(– (EXACT( TEXT(E5, “mmyy”), TEXT( B5:B19, “mmyy”)) ), C5:C19)

It will return to 60000.

In both cases, the date given in cell E5 is first converted to “MMYY” format, and the dates given in cells B5:B19 are also converted to the same format and then matched. In the first case, wherever there is a match, it will return “TRUE” or “FALSE,” making an array. This array is then converted to numbers using ISNUMBERISNUMBERISNUMBER function in excel is an information function that checks if the referred cell value is numeric or non-numeric.read more, returning an array of 0 and 1. The SUMPRODUCT function will then count the number of 1 (i.e., TRUE, i.e., where we found the match), returning the number of transactions. The EXACT function will get the corresponding value wherever there is a match in the latter case. The SUMPRODUCT function will then sum the total transactions where we obtained a match.