How to Format Text as Dates using Find & Replace (Tutorial)

Introduction

Hey there Anylearners! During your experience with data cleansing in Excel, I’m relatively confident that you will encounter some problems with the data format and how to convert them. In this tutorial, you will learn how to format text as dates, which is a common issue for beginners in Excel.

More frequently, data is exported directly from different sources, ranging from databases, tables scraped from webpages as well as various financial software. Consequently, some incorrect formatting will occur when the values are imported into Excel.

Therefore, I’ve done the tough work for you and did some research online for the fastest and most effective method for resolving date formats, especially for converting text to date. Most likely, this problem can be solved with many possible solutions. However, using the Find and Replace feature stood out to me as the most intuitive and beginner-friendly.

I will cover this method in an example further in this post. Let us understand more about how the formats in Excel are detected, before proceeding with an example.

Why are my dates formats not recognized?

A common problem encountered by many is having incorrect dates derived from the data not being recognized as a date in Excel. The dataset below shows a typical example of how the dates are not being read as the date format.

In the screenshot image above, the dates shown in Column A are not being recognized as dates. Although it may seem like dates to you, Excel does not register the numbers as dates. Often, these dates were taken from a different table or data environment that recognizes dates differently.

However, a quick tip for noticing this problem would be observing the alignment of the date values. Date, time, currency and other numerical formats in Excel align values to the right of the cell. The text format aligns values to the left of the cell.

In this example, Column A is occupied with data in the Text format. To show these dates properly, these have to be converted into the Date format.

How do I know the format of my dates?

You can do a simple check on your dates by following these steps:

1. Select any cell with your dates
2. Select the Home tab
3. Navigate to the Number section
4. Select the Dropdown list

The drop-down box shows the format of the current selected cell. As shown in the example above, my dates have the Text format. This means that the Text-formatted data needs to be converted into Date-formatted data.

How to Format Text as Dates?

Now here’s a great trick for quickly solving this simple problem – using the Find and Replace function within Excel! Here’s how:

This should convert your dates from text into the date format that we needed!

Of course, there would be multiple ways for this to be done. Personally, this would be the easiest and fastest method that does not require much manual manipulation of the data. The Find and Replace function forces Excel to evaluate all cells that contain the “/” or “-” symbols and assign the correct data format type accordingly.

In conclusion, when trying to convert your text to date format, do check what the format of your dates are and consider using the Find and Replace tool on Excel to fix it. Hope this tutorial helps you!

Useful Shortcut Tip for Find and Replace

• Use Ctrl + H as a quick shortcut to activate the Find and Replace dialog window.