Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Convert Numbers to Text using Excel (Table of Contents)
- Convert Numbers to Text in Excel
- #1 – Use Text Function in Excel
- #2 – Using Format Cell Option
- Case Studies & Applications
How to Convert Numbers to Text in Excel?
When entered any number, Excel automatically changes its formatting to number format. Similarly, any mathematical operation returns a number in number formatting. Many times the number formatting may not be required and needs to convert back to the text. It may be also required to display numbers in a more readable format, or to combine numbers with text or symbols. In such scenarios, the number formatting has to be changed to text.
To convert numbers to text in Excel, there are two different methods:
- Use “TEXT” function in Excel
- Use the Format Cell option
Below are the two methods through which you will learn how to convert numbers to text in excel.
Method #1 – TEXT Function in Excel
The TEXT function in Excel is used to convert numbers formatted as Number to text in the specified format.
TEXT Formula in Excel
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 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”.
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 in excel format.
You may give the Text Formula as:
= TEXT (B4, “0”)
It will return the number in text format.
Suppose you have a number 0.781 in cell B5 and you want to convert this number to a percentage.
You can use the following TEXT Formula:
= TEXT (B5, “0.0%”)
This will return 78.1% as text format.
Suppose you have a number 21000 in cell B6 and you want to display the number as currency in the dollar.
To do this, you can use the TEXT Formula:
= TEXT (B6, “$0”)
It will return $21000.
Suppose you have a date 10/22/2018 in cell B7 and you to convert this date to another format.
You can use the TEXT Formula:
= TEXT (B7, “mmmm dd, yyyy”)
It will return the date in the specified i.e., October 22, 2018.
Method #2 – 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 in excel.
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.
It clearly mentions that the cell is currently formatted as a number. Now, select “Text” option in the Category.
and click “OK” and you will notice that the number has been formatted as Text now.
Similarly, you can change the formatting to a percentage, date, scientific notation etc.
Suppose you want to convert the cell B13 to percentage format.
Select the cell and press CTRL + 1 (or COMMAND + 1 for Mac) and select Percentage
and click on “OK”.
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.
As shown in the above steps, you can select 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.
Convert Numbers to Text Excel Example #1
Suppose you have the start and end timings of different sessions going to be held in the upcoming workshop organised by your company as shown below. You are supposed to merge the two time creating a range (time duration).
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” )
It will return the time range for the 1st session.
Now, you can drag it to 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 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.
Convert Numbers to Text Excel Example #2
Suppose you have a 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 transactions 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, you can use the SUMPRODUCT Formula:
= SUMPRODUCT( –(( ISNUMBER ( FIND ( TEXT (E5, “MMYY”), TEXT ( B5:B19, “MMYY”))))))
and press Enter.
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)
It will return 60000.
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.
Things to Remember About Convert Numbers to Text in Excel
- To convert numbers to text in excel, the TEXT function in Excel can be used.
- The text function requires value and format_cell. The format_cell is provided in double quotes.
- To convert numbers to text, the format cell option can also be used
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 –
- Leading Zeros Excel
- How to Separate Text in Excel?
- Text in Excel Formula
- Merge & Center (Excel Shortcut)
- Types of Excel Cell Reference
- Excel Auto Numbering
- Numbering in Excel – Using Fill Series
- Excel Combine Cells
- Merge Cells in Excel
- Paste Special in Excel
- OFFSET Excel Function
- LARGE Excel Function
- INDIRECT Function in Excel