Table of Contents
In order to convert a date to text without losing the formatting in Excel, there are a few simple steps to follow. First, select the cells containing the dates you want to convert. Then, right-click and choose “Format Cells” from the menu. In the Number tab, select “Text” as the category. This will convert the dates to text while retaining the original formatting. Another option is to use the TEXT function, which allows you to specify the format you want the date to be displayed in as a text string. This can be done by typing “=TEXT(cell reference, “format code”)” in a new cell, where the cell reference is the date you want to convert and the format code is the code for the desired date format. By following these steps, you can easily convert dates to text in Excel without losing the formatting.
Excel: Convert Date to Text Without Losing Format
You can use the following formula to convert a date to text without losing format in Excel:
=TEXT(A2, "dd-mm-yyyy")
This particular formula will convert the date in cell A2 to text while using dd-mm-yyyy as the date format.
Note that by using the TEXT function, you can specify any date format you’d like.
The following example shows how to use this formula in practice.
Example: Convert Date to Text Without Losing Format in Excel
Suppose we have the following list of dates in Excel that currently have a dd-mm-yyyy date format:
Suppose we would like to convert each of these dates to text without losing the format they’re currently in.
To do so, we can type the following formula into cell B2:
=TEXT(A2, "dd-mm-yyyy")
We can then click and drag this formula down to every remaining cell in column B:
Column B displays each date in column A in a dd-mm-yyyy format.
We can verify that each cell in column B is in a text format by typing the following formula into cell B2:
=ISTEXT(B2)
We can then click and drag this formula down to each remaining cell in column B:
Since each cell in column B returns TRUE, this confirms that each value in column B is indeed formatted as text.
Note: You can find the complete documentation for the Excel TEXT function .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: