Convert Date to Text in Excel: A Comprehensive Guide

If you write dates in excel but it turns into some number. Then, you have to convert date to text in Excel. . Converting date values to text is a common task for every level of Excel user. And good news isi there are several ways to do tough. So we will explore different methods for converting datesto text.

1. How to Convert excel date to text format: Introduction to excel date to text format

Excel is a popular tool and widely used in different industries. One of those tasks is converting date values to text. Converting date values to text can be useful when you want to display dates in a specific format. Or when you want to use dates as part of a text string.

We will cover both manual methods and automated methods using VBA macro and Power Query here below to Convert Date to Text in Excel.

2. Convert Date to Text in Excel: Why Convert Dates to Text in Excel?

You might want to convert excel date to text format for several reasons like below.

• For displaying dates in a specific format.
• In case you wanna use dates as part of a text string.
• If you wish to import data into other programs but they do not support date formats.
• And also to create custom reports or charts wanting text instead of date formats.

3. Method 1: Using Text Function: how to convert date to text in excel

The Text function is a built-in function in Excel. So it converts a value to text in a specific format. If you wish to convert a date to text using the Text function, you can follow these steps.

1. Firstly, you will select the cell or cells with dates you want to convert.
2. Now, you will press Ctrl+1 to open the Format Cells dialogue box.
3. In the Number tab, you select Custom from the Category list.
4. In the Type box, then you will enter the format code for the date format you want to use. For example, if you want to display dates in the format “dd-mmm-yyyy”, you can enter “dd-mmm-yyyy” in the Type box.
5. And now you can click OK.

The dates in the selected cells will now be in the format you specified.

4. Method 2: Using Format Cells Dialog Box

The Format Cells dialog box can convert date values to text.

1. After reding first 2 steps above, now you can go Number tab and select Text from the Category list.
2. After you just click OK.

The dates in the selected cells will now be text.

5. Method 3: Using CONCATENATE Function

The CONCATENATE function can combine date values with other text values. If you wish to convert a date to text using the CONCATENATE function, you can do below.

1. Like other methods, you should Select the cell for converted version.
2. Now, you can type the following formula: =CONCATENATE(“Text value”, A1). After, you will replace Text value with the text you want to combine with the date value. And then, you replace A1 with the cell containing date value.
3. Now, you can press Enter.

The date value in the selected cell will now be combined with the text you gave.

6. Method 4: Using Custom Number Formatting

Custom number formatting can convert date values to text. So if you wanna do this, follow these steps:

1. After following same first steps, you can select Custom from the Category list.
2. In the Type box, you will enter the format code for the date format you want. For example, if you want to display dates in the format “dd-mmm-yyyy”, you enter “dd-mmm-yyyy” in the Type box. And then, you can add the “@” symbol at the end of the format code. An just click OK.

7. Method 5: Using Power Query

Power Query also can transform and shape data. To convert date values to text using Power Query, you can do below steps.

1. As always, you select the range of cells with dates.
2. You will click on the Data tab and then click From Table/Range.
3. In Power Query Editor window, you select the column with dates.
4. You can click on Transform tab first and then click Data Type.
5. Now, you need o select Text from the drop-down list.
6. Lastly, click “Close & Load” to apply the changes and return to Excel.

8. Method 6: Using VBA Macro for excel date to text format

VBA Macro is a programming language in Excel. And you can use it to automate tasks. To convert date values to text using VBA Macro, you will do the below things.

1. Again now, you press Alt+F11 to open the VBA Editor.
2. Afterward, you will click Insert from the menu and then click module this time.
3. And just press F5 to run the macro.

9. Conclusion: change date to text in excel

Now, you have manual and automated methods using VBA macro and Power Query. If you use these methods, you can easily convert date values to text in a specific format or as part of a text string.

10. FAQs: how do i convert a date to text in excel

1. Can you convert multiple date values to text at once? Yes, you can select a range of cells with date values and apply any of the methods above.
2. What about converting text values to date values in Excel with formula? You can use the DATEVALUE function to convert text values to date values.
3. What formulas to convert date values to text? You can use various formulas. These are such as TEXT function for converting dates.
4. Can we use custom number formatting to display date values in different formats? You can apply custom number formatting to show dates in various formats.
5. How to automate the conversion of date values to text using VBA Macro? You can write a VBA macro to automate the conversion of date values to text.

You can read one of other blogs namelyWhich Version of Excel Has 3D Maps. And you can learn more about Excel. You can also check the other content.