How To Grab The First Date Out Of A String Of Text In Excel

In this guide, we're going to show you how to extract appointment from cord in Excel by using formulas and VBA.

Download Macro-Free Workbook

Download Workbook

Note that, in that location isn't a single formula or approach that can handle all scenarios. Extracting dates from strings that is not following a specific pattern is one of the few things Excel cannot handle. You almost demand an AI to exist able to encapsulate every possible scenario. The expert thing is that we are going to offer you multiple ways to parse the date.

Data

Nosotros have generated strings that included dates using slash (/) as day-calendar month-year separator and using U.s.-type (calendar month/twenty-four hours/year) dates. Thus, if your date format is dissimilar, y'all may see errors (empty cells because of IFERROR functions) after opening our file. Update the dates according to your localization settings. As well, exercise non forget to update the slash characters if your data includes dates with other separators like dot (.) or hyphen (-).

Formula for extracting date from string

The logic behind the formulas nosotros will introduce is to locate the pattern that resembles a date. Once the potential date value'southward location is found, nosotros tin parse it easily.

The tricky part is to select a right pattern which is suitable with your raw data. A date tin can be in many forms:

• 01/07/2022
• i/7/2022
• 1/seven/22
• 1/12/2022
• 12/1/2022
• 12/i
• 12/2022

If your raw data contains more than than one or two patterns the formula solution may not work unless you do not want to create extremely long formulas.

In our sample formula, we used "01/07/2022" format. Thus, we assume the date values in the data to follow the rules beneath:

• US Date format
• Month and day are two digits
• Twelvemonth uses four digits
• Each unit is separated by slash "/"
• A date contains 10 characters including delimiters

Based on these assumptions, we tin use "/??/" keyword with the SEARCH role to locate the appointment. The SEARCH part tin utilise the wildcards to locate a blueprint instead of an exact string. Because a question mark (?) represents a single character, "/??/" refers 2 characters between slashes just like the 24-hour interval part of a date.

The SEARCH part returns the position number where the keyword starts if there is a matching string.

Change the blueprint keyword based on your data. For example, ".??." or ??-????

Decrease the position by 2 to find where the date starts.

Once the date is located, you can utilise the MID function to parse information technology. The MID role needs the position of the string you want to parse and its length. Because we assume the date format is "mm/dd/yyyy", the length will be x.

=MID(<data>,SEARCH("/??/",<data>,1)-two,<date length>)

Although, the formula looks like its working, it will return whatever characters matching the design i.e. letters.

This is where the DATEVALUE function comes handy. The function can convert text-based appointment/time values into actual dates. The office returns #VALUE! error if the supplied text is not a valid date. You can apply the DATEVALUE role to verify dates. The IFERROR function tin wrap the formula to cover scenarios without date.

=IFERROR(DATEVALUE(MID(<data>,SEARCH("/??/",<data>,ane)-2,<date length>)),"")

Although this formula is suitable for our example, it's obvious that it will fail for others. A few examples:

• A single-digit month date without a space in front: cells C6, C9
• A text containing similar string before the actual date: cell C8
• Single-digit month and days: cells between C19 and C25
• Double-digit years: cells betwixt C26 and C32

Using VBA for extracting date from string

An alternative to the Excel formulas is to create your own function by using VBA. The VBA-based functions, also known equally user defined functions (UDF), tin exist used as regular formulas once they are written and saved properly.

VBA has two dedicated methods which give a item advantage for extracting a date from a string.

1. The Split up method can separate words or strings separated past spaces.
2. The DateValue method can validate dates meliorate than its formula counterpart.

Patently, this method is non foolproof neither. The date values should be separated from other text by a space grapheme. On the other manus, you are non limited with a sure pattern. Thus, number of digits in units are not meaning.

You can copy and paste the following code into a module and start using it correct away. Simply do not forget to salve your Excel file as an Excel Macro-Enabled Workbook (XLSM) rather than a regular workbook.

Function ExtractDate(str Equally String)        On Error GoTo errHandler    Dim i As Integer, dt As Date, arr() Equally String, temp Every bit String     dt = 0    arr = Split(str)     For i = LBound(arr) To UBound(arr)       temp = arr(i)       If IsDate(temp) And Len(temp) > v Then          dt = DateValue(temp)          Exit For       End If    Next i  cont:    ExtractDate = dt    Exit Part  errHandler:   dt = 0   Resume cont  Stop Function

Once you paste the code, use it in your worksheet by giving a reference to the data jail cell.

=IF(ExtractDate(<data>)=0,"",ExtractDate(<information>))

The equals to nil (0) condition is to avoid zero (0) values. Otherwise, the function will render zero (0) when information technology cannot find a valid date.

As you can run across either approach is far from perfection. However, you accept more than one option to excerpt date from cord.

Source: https://www.spreadsheetweb.com/how-to-extract-date-from-string-in-excel/

Posted by: aldrichtrepen.blogspot.com

Share this post