WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Excel Tutorials » Convert Numbers to Text in Excel

Convert Numbers to Text in Excel

How to Convert Numbers to Text in Excel?

To convert numbers to text in Excel, there are two different methods:

  • Method #1 – Use “TEXT” function in Excel
  • Method #2 – Use the Format Cell option

Now let us discuss each of the methods with an example

You can download this Convert Numbers to Text in Excel Template here – Convert Numbers to Text in Excel Template

#1 Convert Numbers to Text using Excel TEXT Function

The TEXT function in Excel is used to convert numbers formatted as Number to text in the specified format.

TEXT Formula in Excel

TEXT Formula in Excel

Arguments

value: The value for which text formatting is required

format_code: The formatting code required 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. You can use “0” to specify an integer with zero decimal place, “0.000” to specify three decimal places, “0000” to add leading zeros, etc. You can also specify 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 a cell B4 containing number 25.00 and formatted as a number. You want to convert numbers to text format.

Convert to number ( Text function illustration - 1)

You may give the Text Formula as:

= TEXT (B4, “0”)

Convert to number ( Text function illustration - 1-1)

It will return the number in text format.

Convert to number ( Text function illustration - 1-3)

Suppose you have a number 0.781 in cell B5 and you want to convert this number to a percentage.

Convert to number ( Text function illustration - 1-2)

You can use the following TEXT Formula:

= TEXT (B5, “0.0%”)

Convert to number ( Text function illustration - 1-4)

This will return 78.1% as text format.

Convert to number ( Text function illustration - 1-5)

Suppose you have a number 21000 in cell B6 and you want to display the number as currency in the dollar.

Convert to number ( Text function illustration - 1-6)

To do this, you can use the TEXT Formula in Excel:

= TEXT (B6, “$0”)

Convert to number ( Text function illustration - 1-7)

It will return $21000.

Convert to number ( Text function illustration - 1-8)

Suppose you have a date 10/22/2018 in cell B7 and you to convert this date to another format.

Convert to number ( Text function illustration - 1-9)

You can use the TEXT Formula:

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

Convert to number ( Text function illustration - 1-10)

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

Convert to number ( Text function illustration - 1-11)

#2 Convert Numbers to Text using Excel Format Cell Option

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

Suppose you have a number 25 in cell B11 and you want to convert numbers to text.

Convert to number ( Format Option - illustration - 1)

To do so, you can press CTRL + 1 (or COMMAND + 1 in Mac). A dialogue box will appear after this similar to the one shown below.

Convert to number ( Format Option - illustration - 1-1)

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

Convert to number ( Format Option - illustration - 1-2)

and click “OK” and you will notice that the number has been formatted as Text now.

Convert to number ( Format Option - illustration - 1-3)

Similarly, you can change the formatting to a percentage, date, scientific notation in excel, etc.

Suppose you want to convert the cell B13 to percentage format.

Convert to number ( Format Option - illustration - 1-4)

Select the cell and press CTRL + 1 (or COMMAND + 1 for Mac) and select Percentage

Convert to number ( Format Option - illustration - 1-5)

and click on “OK”.

Convert to number ( Format Option - illustration - 1-6)

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

Convert to number ( Format Option - illustration - 1-7)

As shown in the above steps, you 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 where you can convert numbers to text in Excel.

Example #1

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

Example 1

To get a range of time, you first need to convert the time in Text format and then merge them. For the first session, you can use the following TEXT Formula:

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

Example 1-1

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

Example 1-2

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

Example 1-3

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 hour: minute format (24 hours). Similarly, TEXT( C4, “hh:mm” ) will convert the given time in cell C4 to hour: minute format.

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

& ( ” Hrs” )      the text Hrs will be added at the end.

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

Example #2

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

Example 2

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

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

Example 2-1

and press Enter.

Example 2-2

Now, to identify the total transaction amount during that period you can use the following  SUMPRODUCT Formula:

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

Example 2-3

It will return to 60000.

Example 2-4

In both the above cases, the date is 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 else FALSE making an array. This array is then converted to numbers using ISNUMBER returning an array of 0 and 1. The SUMPRODUCT function will then count the number of 1 (i.e, TRUE i.e, where the match was found) returning the number of transactions. In the latter case, the EXACT function will get the corresponding value wherever there is a match. The SUMPRODUCT function will then sum the total transactions where a match was obtained.

Recommended Articles

This has been a guide to Convert Numbers to Text in Excel. Here we discuss how to convert numbers using two methods – 1) Text Function and 2) Format Cell Options. Here we also see case studies where you can convert numbers to text in excel for appropriate usage along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

0 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>