Function Problems

Fix Words Sorting in the Wrong Alphabetical Order in Excel

Huda QurayshiHuda Qurayshi Nov 14, 2026 868 views

Question details

The user needs to sort a list of words alphabetically when hidden spaces affect their order.

Fix Words Sorting in the Wrong Alphabetical Order in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Sorting a column of words from A to Z in a worksheet.
Observed behavior
Words that appear to begin with a letter may sort before other entries because some cells contain leading spaces.
Before you start

Identify the column to sort and check whether the first row contains headers. If the words belong to a table, include the related columns when sorting so each row stays together.

Solution 1Recommended

Remove leading spaces with TRIM and sort again

Use a helper column to clean extra ordinary spaces before sorting the list.

1
Add a helper column

Insert an empty column beside the word list. If the first word is in A2, select the corresponding cell in the new column.

2
Enter the TRIM formula

Type =TRIM(A2) and press Enter. Change A2 to the cell containing your first word, then drag the fill handle down to clean the remaining entries.

3
Replace the original text

Copy the helper-column results, select the first original word cell, and use Home > Paste > Paste Special > Values. You can then remove the helper column.

4
Sort the complete range

Select the table, open Data > Sort, choose the cleaned word column, and select A to Z. Indicate whether the range has headers before confirming.

Remove leading spaces with TRIM and sort again
Check for nonbreaking spaces: TRIM removes extra ordinary spaces but not nonbreaking spaces. If the order is still wrong, try =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in the helper column.
Clean and sort spreadsheet text

Sort cleaned words in WPS Spreadsheet

Use a helper column in WPS Spreadsheet to remove extra spaces, replace the original text with cleaned values, and sort the table alphabetically.

  1. 1. Open the spreadsheet: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Clean the text: In an empty column beside the list, enter =TRIM(A2), adjusting A2 to the first word cell, and drag the fill handle down.
  3. 3. Paste cleaned values: Copy the helper-column results and use Paste Special > Values over the original word cells. Remove the helper column if it is no longer needed.
  4. 4. Sort the table: Select the complete table and use Data > Sort to sort the cleaned word column from A to Z, with the appropriate header setting.
Remove leading spaces before sorting words from A to ZOpen and edit Microsoft Excel XLS and XLSX filesUse familiar spreadsheet formulas and sorting controlsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why do some words appear before A when I sort in Excel?

A cell may contain a leading space that is not easy to see. Use =TRIM(A2) in a helper column, paste the results as values, and sort the table again.

Does TRIM remove spaces between words?

TRIM removes leading and trailing ordinary spaces and reduces multiple ordinary spaces between words to a single space. For example, =TRIM(A2) keeps one space between two words.

What if TRIM does not fix the sort order?

The text may contain nonbreaking spaces. In a helper column, try =TRIM(SUBSTITUTE(A2,CHAR(160)," ")), then paste the results as values and sort again.

How can I keep related data together when sorting?

Select the complete table and use Data > Sort to choose the word column. Confirm whether the table has headers so Excel sorts the data rows correctly.