Convert a Date to Text in Excel
This page describes the following three different methods, that can be used to convert a date to text in Excel.
| Excel Convert Date to Text Index: |
| Date to Text By Adding an Apostrophe |
| Date to Text Using Text to Columns |
| Date to Text Using Excel Functions |
Convert a Date to Text By Adding an Apostrophe
The quickest way to convert an Excel date to text is to insert an apostrophe in front of the date (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.
Convert a Date to Text Using Excel Text to Columns
If you want to convert an entire column of Excel dates to text, you might prefer to use the Excel Text to Columns command.
However, note that the Text to Columns command only works on one column of data at a time.
To use the Excel Text to Columns command to convert dates 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 Date to Text Using the Excel Text Function
The Excel Text Function converts a supplied numeric value (including an Excel date or time) to text, using a user-specified format.
The syntax of the function is:
TEXT( value, format_text )
where,
- value is the numeric value 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 the date 01/01/2016 into text strings with various date formats:
|
Formula:
|
Result:
|