Function Problems

How to Remove Lowercase Letters from Excel Cells While Keeping Numbers

Algirdas Jasaitis Oct 12, 2026 871 views

Question details

The user needs to remove all lowercase letters from a text string in a spreadsheet cell, ensuring that only uppercase letters and numeric values remain.

How to Remove Lowercase Letters from Excel Cells While Keeping Numbers

Product: Excel
Device & OS: not provided
Scenario: Cleaning up and standardizing mixed-case alphanumeric data within a spreadsheet column.
Observed behavior: The target cell contains a mix of uppercase letters, lowercase letters, and numbers. The goal is to eliminate the lowercase characters without affecting the rest of the data.

Before you start

Ensure you are using a modern version of your spreadsheet software that supports dynamic array functions. Always test formulas in an adjacent, new column before overwriting your original data.

Solution 1Recommended

Use the TEXTSPLIT and SEQUENCE Formula

This method uses modern dynamic array functions to automatically identify and strip out all lowercase English letters from a cell.

This approach uses the SEQUENCE function to generate an array of lowercase letters (ASCII 97 to 122) and uses them as delimiters in the TEXTSPLIT function. The CONCAT function then recombines the remaining characters.

Select an adjacent cell

Click on cell B2, right next to the data in A2 that you want to clean.

Enter the formula

Type the following formula into the formula bar: =CONCAT(TEXTSPLIT(A2&"X",CHAR(SEQUENCE(26,,97)),,1))

View the result

Press Enter. The cell will now display the original string but with all lowercase letters removed.

Apply to the entire column

Click the small square at the bottom-right corner of cell B2 and drag it down (or double-click it) to apply the formula to the rest of your data.

Function Compatibility: This formula relies on functions like TEXTSPLIT and SEQUENCE, which are available in newer spreadsheet software updates. If you receive a #NAME? error, your version may not support these functions.

Solution 2

Use a Regular Expression (RegEx) Replacement Tool

If you have RegEx functions enabled through add-ins or supported software, you can use pattern matching to remove specific character types.

Easily Manage Complex Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing capabilities, including advanced text manipulation and an intuitive interface, making data cleanup tasks highly efficient.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the workbook containing your mixed-case data.
2. Select a blank cell: Click on the cell next to your target data to begin writing the extraction formula.
3. Apply the text formula: Enter the text extraction or string manipulation formula and hit Enter to process the data.
4. Fill down to complete: Drag the fill handle downward to instantly clean up the entire dataset.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Includes comprehensive support for advanced string manipulation and logic formulas.Lightweight, fast, and free to use across Windows, Mac, and Linux.Familiar user interface ensuring seamless migration with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is an 'X' added to the formula string?

The &"X" portion of the formula acts as a safety buffer. If the original string ends with a lowercase letter (which acts as a delimiter), the TEXTSPLIT function might drop the last split or behave unexpectedly. Adding the 'X' prevents this, and since it is an uppercase letter, it remains in the final string until managed, or it ensures the trailing delimiter is processed correctly.

What does CHAR(SEQUENCE(26,,97)) do?

This combination of functions generates a dynamic array of 26 characters. The SEQUENCE function creates an array of numbers from 97 to 122. The CHAR function then translates these ASCII codes into the lowercase letters 'a' through 'z', creating the exact list of delimiters needed to split and remove the lowercase text.

Can I use Find and Replace instead of a formula?

While you can use the standard 'Find and Replace' tool by checking the 'Match case' option, you would have to run it 26 separate times—once for every lowercase letter in the alphabet. Using a formula is significantly faster and automatic.

Will this remove special characters or punctuation?

No, the formula specifically targets ASCII values 97 through 122, which only encompass the standard lowercase English alphabet. Numbers, uppercase letters, spaces, and special symbols like dashes or commas will remain perfectly intact.

Excel & Data Problems