Function Problems

How to Sort Excel Values by Text After a Colon

Ayan Masood Sep 27, 2026 871 views

Question details

The user needs to sort combined inventory bin values based solely on the text string that appears after a colon, ignoring the leading numbers.

How to Sort Excel Values by Text After a Colon

Product: Excel
Device & OS: not provided
Scenario: Organizing inventory lists where the critical location identifier is located after a colon delimiter (e.g., 1000:A01B19).
Observed behavior: By default, sorting applies to the entire cell value starting from the first character, resulting in an incorrect order based on the preceding numbers rather than the actual bin location.

Before you start

Ensure your dataset is organized without merged cells and verify that all target cells contain a colon delimiter to prevent formula extraction errors.

Solution 1Recommended

Use a Helper Column with the TEXTAFTER Function

The most straightforward method is to extract the text after the colon into a new helper column and sort your dataset based on this new column.

This method creates a clear, visible column for the extracted data, making it easy to verify that the extraction is correct before applying the sort.

Insert a helper column

Right-click the column header next to your inventory data and select 'Insert' to create a new blank column.

Enter the TEXTAFTER formula

In the first cell of the new column (e.g., B2), type the formula =TEXTAFTER(A2, ":") and press Enter. This extracts everything after the colon.

Apply formula to the rest of the column

Click the bottom-right corner of the cell containing the formula and drag it down to fill the remaining rows.

Sort the data

Select your entire data range (including both the original and helper columns). Go to the 'Data' tab, click 'Sort', and choose to sort by the new helper column.

Use a Helper Column with the TEXTAFTER Function

Tip: If you notice locations sorting incorrectly (e.g., A01A01 out of order), check for and remove any hidden, leading, or trailing spaces after the colon. You can wrap the formula in TRIM like =TRIM(TEXTAFTER(A2, ":")) to fix this automatically.

Solution 2

Apply Dynamic Sorting with SORTBY and FILTER

Use a combined dynamic array formula to automatically extract and sort your data in a single step without altering the original dataset.

Solution 3

Split and Sort Cells with Multiple Bin Locations

If a single cell contains multiple inventory locations separated by commas, you must split the text before extracting the values after the colon.

Data Sorting in WPS

Efficiently Manage and Sort Complex Data with WPS Spreadsheet

WPS Spreadsheet offers powerful built-in text functions and advanced sorting capabilities, making it easy to organize complex inventory lists and extract specific data strings without hassle.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your inventory list.
2. Use the extraction formula: Insert a column next to your data and enter =TEXTAFTER(A2, ":") to extract the location.
3. Apply custom sorting: Highlight your dataset, navigate to the Data tab, select Sort, and set your new column as the primary sorting key.

Full support for advanced text extraction functions like TEXTAFTER and TEXTSPLIT.100% compatibility with Microsoft Excel formulas and .xlsx file formats.Intuitive Data Sorting menu to easily manage multi-column lists.Lightweight architecture ensures fast calculation for large dynamic array formulas.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my sorted data out of order even after extracting the text?

Incorrect sorting is typically caused by leading, trailing, or hidden spaces around the extracted text. This commonly happens if there is a space after the colon in your original data. You can fix this by wrapping your extraction formula in the TRIM function: =TRIM(TEXTAFTER(A2, ":")).

What happens if some cells do not contain a colon?

If a cell lacks the specified colon delimiter, the TEXTAFTER function will return a #N/A error. You can handle this gracefully by wrapping your formula in an IFERROR function, such as =IFERROR(TEXTAFTER(A2, ":"), A2), which will return the original text if no colon is found.

Can I extract and sort by the text before the colon instead?

Yes, you can extract the text preceding the delimiter by using the TEXTBEFORE function. Simply replace TEXTAFTER in your formula with TEXTBEFORE (e.g., =TEXTBEFORE(A2, ":")) and sort by the resulting values.

Excel & Data Problems