Data Import & Export

How to Split Multiple Keywords in One Excel Cell for Analysis

Kushani Nimanthika Oct 12, 2026 868 views

Question details

The user needs to separate multiple hashtags or keywords located within a single cell into distinct cells so they can be analyzed individually.

How to Split Multiple Keywords in One Excel Cell

Product: Excel
Device & OS: not provided
Scenario: Organizing survey results, social media data, or descriptive tags where multiple keywords are grouped together in a single cell.
Observed behavior: The spreadsheet treats multiple keywords in one cell as a single text string (e.g., '#red #blue' is read differently than '#blue #red'), preventing accurate counting or sorting of individual tags.

Before you start

Ensure there are enough empty columns immediately to the right of your target column to accommodate the split data so that existing information is not accidentally overwritten.

Solution 1Recommended

Use the Text to Columns Feature

This built-in data tool easily separates cell contents into multiple columns based on a specific delimiter, such as a space or a hashtag (#).

Text to Columns is the most efficient way to break apart strings of text without writing complex formulas. It reads your chosen separator and moves the subsequent text into the next adjacent cell.

Select the target column

Highlight the specific column containing the multiple keywords or hashtags you want to split.

Open Text to Columns

Go to the Data tab on the top ribbon menu and click on 'Text to Columns'.

Choose Delimited

In the Convert Text to Columns Wizard, select 'Delimited' as the file type that best describes your data, then click Next.

Set the Delimiter

Check the 'Other' box and enter the hashtag symbol (#) or a space, depending on how your keywords are separated. Click Next.

Finish the split

Choose the data format for your new columns and click Finish. Each keyword will now be extracted into its own separate cell.

Handling Extra Spaces: You may want to select 'Treat consecutive delimiters as one' in the wizard to avoid blank columns if your keywords have extra or irregular spaces between them.

Efficient Data Management with WPS Office

Easily Split and Analyze Data with WPS Spreadsheet

WPS Spreadsheet offers powerful data processing tools, including an intuitive Text to Columns wizard that works exactly like Microsoft Excel. You can quickly separate tags, analyze keywords, and manage large datasets for free.

1. Open your data in WPS: Launch WPS Spreadsheet and open your .xlsx or .csv file containing the combined keywords.
2. Select Text to Columns: Highlight your data column, navigate to the Data tab on the top menu, and click the Text to Columns icon.
3. Define your delimiter: Choose Delimited, specify the hashtag (#) or space as your separator in the wizard, and click Finish to instantly split the text across multiple columns.

100% format compatibility with Microsoft Excel (.xlsx, .xls, .csv) files.Intuitive Text to Columns wizard for quick and accurate data splitting.Free, lightweight, and fast processing for managing large datasets.Built-in pivot tables and charts for advanced keyword analysis post-split.

microsoft office alternative - wps office

Frequently Asked Questions

Why is Excel treating my keywords as a single value?

Because all the text is contained within one cell boundaries. Excel reads the entire cell's content as a single literal string of text, which is why '#red #blue' is not recognized as two separate tags until you split them.

Can I split keywords using formulas instead of Text to Columns?

Yes, you can use a combination of text functions like LEFT, RIGHT, MID, FIND, and LEN to extract keywords. In newer spreadsheet versions, you can also use the TEXTSPLIT function to achieve this dynamically.

What happens if I don't have enough blank columns to the right?

If you run the Text to Columns wizard without inserting enough blank columns next to your data, the newly split data will permanently overwrite the existing data in those adjacent columns.

How do I count the split keywords after separating them?

Once the keywords are split into separate columns, you can consolidate them into a single master column, then use a Pivot Table or the COUNTIF function to analyze the frequency of each individual keyword.

Excel & Data Problems