Convert a Number to Text in Excel
This page describes the following four different methods that can be used to convert a number to text in Excel:
| Excel Convert Number to Text Index: |
| Number to Text By Adding an Apostrophe |
| Number to Text Using Text to Columns |
| Number to Text Using Excel Concatenate |
| Number to Text Using Excel Functions |
Convert a Number to Text By Adding an Apostrophe
The quickest way to convert an Excel number to text is to insert an apostrophe in front of the number (see rightabove).
The apostrophe will not be displayed in the cell, but it will tell Excel to treat the contents of the cell as text. This is also a useful way to force Excel to display leading zeros on numbers such as telephone numbers.
Convert a Number to Text Using Excel Text to Columns
If you want to convert an entire column of Excel numbers to text, you might prefer to use the Excel Text to Columns command.
However, this method might be less useful if your data spans several columns, as the Text to Columns command only works on one column at a time.
To use Excel Text to Columns to convert numbers to text:
- Select the range of cell(s) that you want to convert (these must not span more than one column).
-
From the Data tab on the Excel ribbon, select the Text to Columns option (see rightabove).
This will cause the Convert Text to Columns wizard to open up. Within this:
- Make sure the Delimited option is selected and click Next >;
- Make sure that none of the Delimiters are selected and then click Next > again;
-
You should now be offered a selection of Column Data Formats. Select Text and click the Finish button.
Convert a Number to Text Using Excel Concatenate
The concatenation operator & simply joins together text and numbers into a single text string.
Therefore, if you concatenate an empty text string and a number, this returns a text string containing just the number.
An example of this is shown in the following spreadsheet:
|
Formula:
|
Result:
|
As required, the values in column B of the results spreadsheet are text representations of the numbers in column A.
Convert a Number to Text Using Excel Functions
Excel provides the following three functions that are designed to convert numbers into text values:
| Text Function | - | Converts a supplied value into text, using a user-specified format. |
| Dollar Function | - | Converts a supplied number into text, using a currency format. |
| Fixed Function | - | Converts a supplied number into text, rounded to a specified number of decimal places. |
Examples of each of these are provided below:
Convert a Number to Text Using The Excel Text Function
The Excel Text Function converts a supplied number to text, using a user-specified format. The syntax of the function is:
TEXT( value, format_text )
where,
- value is the number to be converted to text;
- format_text is a text string that defines the formatting to be applied to the supplied value.
The spreadsheet below shows examples of the Excel Text Function used to convert a simple number, a currency, a date and a time into text:
|
Formula:
|
Result:
|
The Excel Text Function, and the available formatting options, are described in further detail on the Excel Text Function page.
Convert a Number to Text Using The Excel Dollar Function
The Excel Dollar Function rounds a number to a specified number of decimal places and converts the rounded number to text, using a currency format. The syntax of the function is:
DOLLAR( number, [decimals] )
where,
- number is the number to be converted to text;
- [decimals] is an optional argument that specifies the number of decimal places to be shown after the decimal point. (If omitted, the [decimals] argument is set to the default value 2).
Column B of the following spreadsheet shows three examples of the Excel Dollar Function:
|
Formula:
|
Result:
|
Note that the currency format used in the Dollar function result will depend on the settings on your computer.
Convert a Number to Text Using The Excel Fixed Function
The Excel Fixed Function rounds a number to a specified number of decimal places and converts the rounded number to text. The syntax of the function is:
FIXED( number, [decimals], [no_commas] )
where,
- number is the number to be converted to text;
- [decimals] is an optional argument which specifies the number of decimal places to be shown after the decimal point (if omitted, the [decimals] argument is set to the default value 2);
- [no_commas] is an optional logical argument which specifies if the returned text should separate groups of digits (thousands, millions, etc.) by commas (TRUE = no commas; FALSE or omitted = include commas).
The spreadsheet below shows three examples of the Excel Fixed Function:
|
Formula:
|
Result:
|