Function Problems

How to Extract a Six-Digit DDMMYY Date from Text in Excel

Tauseeq Magsi Sep 30, 2026 870 views

Question details

The user needs to extract a six-digit date (DDMMYY format) embedded in a text string, typically following a hyphen in an attendance code.

How to Extract a Six-Digit DDMMYY Date from Text in Excel

Product: Excel
Device & OS: not provided
Scenario: Parsing mixed text strings, such as attendance or transaction codes, to accurately retrieve date information without using external services.
Observed behavior: Requires a reliable, formula-based solution to isolate the target six digits without relying on VBA macros or web-based regular expressions.

Before you start

Ensure your text data follows a consistent pattern, such as the target date always appearing immediately after the last hyphen in the string. Verify that you are using a modern spreadsheet version that supports dynamic text functions.

Solution 1Recommended

Extract the Date Using Modern Text Formulas

Use built-in dynamic text functions to efficiently extract the exact text between a hyphen and a period without complex scripting.

This method is highly recommended for casual Excel users because it avoids VBA and external services. It relies on finding the text directly after the final hyphen and before a subsequent period.

Select the target cell

Click on the empty cell where you want the extracted six-digit date to appear.

Enter the extraction formula

Type the formula =IFERROR(TEXTBEFORE(TEXTAFTER(A1, "-", -1), "."), "") assuming your original text is in cell A1.

Apply to other cells

Press Enter to apply the formula. Click the bottom-right corner of the cell and drag the fill handle down to extract dates for the rest of your data.

Extract the Date Using Modern Text Formulas

Adjusting Delimiters: If your text format varies, you can easily change the "-" or "." in the formula to match the actual delimiters surrounding your date.

Solution 2

Extract the Date Using Power Query

For larger datasets or when multiple six-digit numbers exist, use Power Query to split and validate the text reliably.

Efficient Data Extraction with WPS Office

Extract Dates and Text Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides a comprehensive set of text functions and data processing tools, allowing you to parse complex text strings like attendance codes and extract exactly what you need with ease.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your text strings and attendance codes.
2. Select a destination cell: Click on a blank cell adjacent to the column containing your first text string.
3. Input the text extraction formula: Enter a text extraction formula tailored to your delimiters, such as =MID(A1, FIND("-", A1)+1, 6), to pull the exact six-digit sequence.
4. Fill the remaining rows: Press Enter to view the result, then double-click the fill handle in the lower-right corner of the cell to apply the formula to the entire column automatically.

Fully compatible with Microsoft Excel formulas and text functionsLightweight application that runs smoothly on any deviceFree to use with an intuitive, familiar interfaceBuilt-in powerful data manipulation tools like Text to Columns and Power Query alternatives

Frequently Asked Questions

What if my spreadsheet version doesn't support TEXTBEFORE and TEXTAFTER?

If those functions are unavailable, you can use a combination of traditional functions like MID, FIND, and LEN. For example, if the date is always the last 6 characters after a hyphen, a formula like =RIGHT(A1, 6) or utilizing =MID(A1, FIND("-", A1)+1, 6) works perfectly.

How can I convert the extracted six digits into a recognizable date format?

Once you extract the DDMMYY text, you can convert it into a true date serial number using the DATE, LEFT, MID, and RIGHT functions. For instance: =DATE(RIGHT(B1,2)+2000, MID(B1,3,2), LEFT(B1,2)), assuming the extracted text is in cell B1.

Can I use the Text to Columns feature instead of a formula to extract the date?

Yes. Under the Data tab, select Text to Columns, choose the 'Delimited' option, and set the hyphen as your delimiter. This will automatically split your text into separate columns, successfully isolating the date string.

Excel & Data Problems