At times you might find yourself working with spreadsheets that have jumbled up data. For example, a single cell holds 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: 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 2: 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.
Once you click finish the data will be extracted to the next empty cell
This article has provided ways of extracting data from a string of text. You can choose either of the methods to modify your spreadsheet and still get the same result.