How to Search Dates and Course Titles Separated by Semicolons in Excel
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.

- 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.
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.
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.
Select your entire data range. Go to the 'Data' tab on the ribbon and click 'From Table/Range' to open the Power Query Editor.
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.
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.
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.

Perform an Exact Match Search with Ctrl+F
If you do not need to restructure the data and only want to locate a specific course taken on a specific date, you can use the built-in Find and Replace tool.
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. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the combined course data.
- 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. Split by delimiters: Choose the 'Delimited' option, select 'Semicolon' as your delimiter to separate the data into different columns.
- 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.

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.




