Power Query Problems

How to Search Dates and Course Titles Separated by Semicolons in Excel

Anum MushtaqAnum Mushtaq Oct 19, 2026 871 views

Question details

The user needs to effectively search or filter Excel cells that contain multiple date and course title pairs combined together and separated by semicolons.

How to Search Dates and Course Titles Separated by Semicolons in Excel
Product
Excel
Device & OS
not provided
Scenario
Managing a training or course tracking spreadsheet where multiple attendance dates and course titles are lumped into a single cell, making it difficult to analyze.
Observed behavior
The combined, semicolon-separated data structure prevents easy sorting or filtering by specific date ranges, fiscal years, or individual course names using standard data filters.
Before you start

Assess whether you need a permanent data restructuring for complex reporting, or just a quick one-time lookup, as standardizing the data layout will require changing how your columns are organized.

Solution 1Recommended

Normalize Data Using Power Query

The most robust method is to use Power Query to transform the semicolon-separated data into individual rows, then separate the dates and titles into distinct columns for accurate filtering.

By normalizing your data, you create a standard database format (one record per row) that easily supports pivot tables, fiscal year sorting, and advanced filtering.

1
Load data into Power Query

Select your entire data range. Go to the 'Data' tab on the ribbon and click 'From Table/Range' to open the Power Query Editor.

2
Split the column into rows

Select the column containing the combined data. Click 'Split Column' > 'By Delimiter' on the Home tab. Choose 'Semicolon' as the delimiter. Under Advanced Options, select 'Split into Rows' and click OK.

3
Split rows into separate columns

With the newly created individual records, select the column again. Click 'Split Column' > 'By Delimiter' and choose 'Space'. Ensure you select 'Split into Columns' this time.

4
Format and load the data

Rename the newly split columns to 'Date' and 'Course Title'. Change the data type of the Date column to 'Date'. Finally, click 'Close & Load' to import the cleaned, filterable data back into a new Excel sheet.

Normalize Data Using Power Query
Filter Ready: Your data is now fully optimized. You can apply standard auto-filters to the Date column to search by specific fiscal years or months.
Effortless Data Management

Manage Complex Spreadsheet Data Easily with WPS Spreadsheet

WPS Office provides powerful built-in data handling tools to manage, split, and filter complex concatenated data with ease. It allows you to process semicolon-separated records efficiently while maintaining a familiar interface.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the combined course data.
  2. 2. Access Data Tools: Highlight the column with the combined data, navigate to the 'Data' tab on the top ribbon, and click 'Text to Columns'.
  3. 3. Split by delimiters: Choose the 'Delimited' option, select 'Semicolon' as your delimiter to separate the data into different columns.
  4. 4. Apply AutoFilter: Click 'AutoFilter' from the Data tab to easily search, sort, and isolate specific dates or course titles from the newly organized columns.
Seamless compatibility with Microsoft Excel (.xlsx) file formats.Built-in 'Text to Columns' wizard to quickly split complex data by semicolons or spaces.Advanced AutoFilter capabilities to seamlessly search by specific dates or partial text strings.Lightweight, fast, and completely free to use for everyday office tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use standard Excel filters on the semicolon-separated column?

Standard filters treat the entire content of a cell as a single, long text string. Because multiple dates and courses are bundled together, the filter cannot isolate an individual date or course name, making chronological sorting or fiscal year filtering impossible without splitting the data first.

How do I separate dates from text if there are multiple spaces in the course name?

When splitting the data by a space delimiter in Power Query, ensure you choose the 'Left-most delimiter' option. This isolates the date (which comes first) into its own column while leaving the remainder of the course title intact, regardless of how many spaces it contains.

Can I search for a partial text string without splitting the data?

Yes. By pressing Ctrl+F and using wildcard characters (like an asterisk *) in your search query, such as '*Course 01*', you can find cells containing that specific course name regardless of the dates combined with it.