Function Problems

How to Group Words Within a 20-Character Limit Using Excel Solver

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to group a list of unique words into sets that contain no more than 20 characters (including spaces) while maximizing the number of words in each set without any repetition.

Product: Spreadsheet Software
Device & OS: not provided
Scenario: Organizing and combining a list of individual words into strings that adhere to a strict maximum character limit.
Observed behavior: Standard formulas are insufficient for combinatorial optimization, requiring a specialized approach like Solver to find the best word combinations.

Before you start

Ensure that the Solver add-in is enabled in your spreadsheet software, as standard formulas alone cannot calculate the optimal combinations required for this task.

Solution 1Recommended

Use the Solver Add-in with Binary Selection Cells

Set up a mathematical model using binary cells (0 or 1) and use the Solver tool to calculate the maximum number of words that fit within the 20-character limit.

Since a standard formula cannot handle the complexity of combinatorial optimization, the Solver add-in is the most effective approach. This method involves setting up helper columns to calculate lengths, adding spaces, and using Solver to test which combination of words maximizes the count without exceeding the 20-character limit.

You will need to use a 'greedy method', meaning you solve for the first optimal group, remove those words from the list, and run the Solver again for the next group until all words are assigned.

Calculate word lengths

Next to your list of words, use the LEN function (e.g., =LEN(A2)) to calculate the character count of each individual word.

Set up binary selection cells

Create a new column next to the lengths dedicated to binary selection. Leave these cells blank or enter 0 for now. The Solver will change these to 1 when a word is selected.

Create a total length formula

In a separate cell, use the SUMPRODUCT function to multiply lengths by the binary cells. Add the count of the spaces by summing the binary cells and subtracting 1 (e.g., =SUMPRODUCT(B2:B20, C2:C20) + SUM(C2:C20) - 1).

Configure the Solver objective

Go to the Data tab and click Solver. Set the Objective to maximize the cell that sums the total number of selected words (e.g., =SUM(C2:C20)).

Add Solver constraints

Add a constraint so your total length formula cell is <= 20. Add a second constraint selecting your binary column and choosing 'bin' (binary) so they can only be 0 or 1.

Solve and iterate

Click Solve. Copy the words marked with a 1 into your first group. Delete those words from your original list, and repeat the Solver process for the remaining words.

Greedy Algorithm Considerations: This iterative, group-by-group method is a 'greedy algorithm'. While it effectively fills groups sequentially, it may not produce the absolute mathematical global optimum for the entire dataset combined.

Advanced Spreadsheet Features

Solve Complex Grouping Limits with WPS Spreadsheet

WPS Spreadsheet features a powerful, built-in Solver tool that flawlessly handles advanced combinatorial problems, allowing you to easily group text within specific character limits.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the list of words you need to group.
2. Access the Solver Tool: Navigate to the 'Data' tab on the top ribbon and click on 'Solver' to open the optimization parameters window.
3. Define your variables: Set your objective cell to maximize the word count, and designate your binary column as the variable cells.
4. Apply constraints and solve: Add the <= 20 character constraint, run the Solver, and watch WPS Spreadsheet instantly calculate the optimal grouping.

Built-in Solver tool available for complex data optimizationFull compatibility with Microsoft Excel formulas and functionsUser-friendly interface for setting up binary constraintsLightweight and completely free to download and use

microsoft office alternative - wps office

Frequently Asked Questions

Can I group words by character limit using only standard formulas?

No, standard text formulas like CONCATENATE or TEXTJOIN cannot perform the combinatorial optimization required to maximize the word count within a strict limit. You must use an optimization tool like Solver or write a custom VBA macro.

Where do I find the Solver tool in my spreadsheet software?

In most spreadsheet applications, including Excel and WPS Spreadsheet, Solver is located under the Data tab. If you don't see it, you may need to enable it first through the Add-ins section in the software options.

Why do I need to account for spaces in my character count?

When combining individual words into a single string or phrase, spaces are required to separate them cleanly. Each space consumes one character of your 20-character limit, so they must be factored into your total length calculation.

What does a 'binary constraint' mean in the Solver?

A binary constraint restricts the changing variable cells to strictly either 0 or 1. In this scenario, a 1 means the word is mathematically 'selected' for the current group, and a 0 means it is skipped.

Excel & Data Problems