At times you might find yourself working with spreadsheets that have jumbled up data. For example, a single cell holding up all of your information instead of distributing it among different cells or adding columns. You might also come across data from another system where Excel does not easily recognize the dates. It can be hard when you have to extract a date from a string of texts. Sometimes you find it a challenge as the date might even be in the wrong format and position in the first place.
So, how do we extract a date from a text string? In this article, we are going to explain to you the steps of achieving this.
Method 1: Extracting a date using the Date function
The Date Function creates a correct date using three variables: year, month, and day. That is =DATE (year, month, day)
The generic formula is
=DATE (LEFT (text, 4), MID (text, 5, 2), RIGHT (text, 2))
This formula is used to convert the text in an unrecognized date format into a recognized Excel date. It is used to parse the text and assemble a proper date formula based on; DATE, LEFT, MID, and RIGHT. Where the LEFT function will extract the four characters representing a year, the MID extracts characters for the month and the RIGHT represents the two characters for the day.
The formula above will extract the date formula values in terms of the year, month and days separately and correctly assemble them using the DATE function.
Remember, you can customize this format into a different one depending on your preference.
Method 2: Use Find & Replace
We make mistakes by placing full stop characters to separate the year, month, and day. Doing so is a mistake as Excel will not recognize the entry as a date but as a text. You can solve this using the find and replace tool, replace the dots with slashes where Excel will automatically recognize said values as dates. Here's what to do;
1. Select the columns to convert the texts to date values.
2. Click the Home tab and then the Find & Select option.
3. In the open window, select Replace option.
4. In the 'Find what' fields, type the full stop icon, while in the 'Replace with' field, type the slash icon.
5. Lastly, click Replace All. All full stops will be converted to slashes by Excel.
Method 3: Use the Substitute Function
The substitute function cannot transform a text to a date value without the VALUE function. The function =VALUE (SUBSTITUTE (A2, ". ", "/"))
1. Select the text you need to extract the date.
2. Click the Home tab in the main menu ribbon.
3. Select 'Number format.' Click on the dialog box arrow to display it.
4. Select the Date option and choose the preferred format.
5. Click OK.
Method 4: Use Text to Columns
1. Select your range of values
2. Go to the Data tab in the main menu ribbon.
3. Select Text to Columns. A display wizard will appear. Click 'Next' on the first two steps. In the third step, choose the Date field and select the date format to be used in the drop-down list provided.
Method 5: DATEVALUE & DATE Functions
This method of extracting a date is used when a date is stored as a text. The DATEVALUE function will convert the text into a date value, while the VALUE function will convert text into a number value. We use the formulas;
This article has provided many ways of extracting date from a string of text. You can choose either of the methods to modify your spreadsheet and still get the same result.