How to Remove Characters After the First Number in Excel Text Strings with WPS Office
Question details
The user needs an Excel-compatible formula to shorten an alphanumeric text string by locating the first numeric character and returning the substring that ends two characters after that position, such as 9U100 to 9U1 or GF3C06 to GF3C0.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has alphanumeric codes in Excel cells and wants to remove unwanted trailing characters after the first numeric segment.
- Observed behavior
- Text strings contain extra trailing characters after the first numeric segment, and the user wants a formula to return only the leading portion shown in the examples.
Open the workbook in WPS Spreadsheets or Excel and identify the column containing the text values. Confirm whether your version supports dynamic array functions such as SEQUENCE because this determines which formula to use.
Use MID and FIND with an Array Constant to Extract the Leading Text
Best for WPS Spreadsheets and older Excel versions; enter it as an array formula when required.
This formula finds the first digit by searching for 0-9 and returns the text from the start through two positions after that first digit. It matches the examples 9U100 to 9U1 and GF3C06 to GF3C0.
Click an empty cell next to the first text value, for example F2, where the shortened result should appear.
Type =MID(E2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E2&"0123456789"))+2). Replace E2 with the cell containing your text string.
Press Enter. In older spreadsheet versions, if you see a #VALUE! error, click the formula bar and press Ctrl+Shift+Enter to enter it as an array formula.
Use the fill handle to copy the formula down the column for the other text strings.
Use MID with SEQUENCE and MATCH for Dynamic Array Versions
Use this option in Excel 365, Excel 2021, or WPS Office versions that support SEQUENCE and dynamic arrays.
Trim Alphanumeric Text in WPS Office Spreadsheets
WPS Spreadsheets supports Excel-compatible text functions such as MID, FIND, MIN, ISNUMBER, MATCH, and SEQUENCE, so you can apply the same formula logic directly in a free, familiar spreadsheet interface.
- 1. Download WPS Office: Go to the official WPS Office website and download the free Windows, macOS, Linux, Android, or iOS version for your device.
- 2. Open the Excel file in WPS Spreadsheets: Launch WPS Office, click Open, and select the .xlsx or .xls workbook containing the text strings.
- 3. Apply the extraction formula: Select an empty result cell, type =MID(E2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},E2&"0123456789"))+2), and press Enter.
- 4. Copy the formula down: Drag the fill handle down the result column to apply the formula to the remaining alphanumeric codes.
Frequently Asked Questions
Why does the formula keep two characters after the first number?
The accepted examples use +2 in the MID length so the result includes the first number and the following two characters. To stop at the first number only, remove the +2 from the formula; to include only one following character, change +2 to +1.
Does this formula work in older Excel versions?
Yes. The MID and FIND array constant formula can be used in older Excel versions, but you may need to press Ctrl+Shift+Enter instead of Enter to enter it as an array formula.
Will TRIM remove characters after the first number?
No. TRIM only removes leading and trailing spaces from text; it does not remove internal characters or trim text based on numeric positions.
Can I use this in WPS Office Spreadsheets?
Yes. WPS Spreadsheets supports the Excel text functions used in the formula, so you can open the workbook and enter the same formula in a result cell.
What should I do if the formula returns #VALUE!?
Select the formula cell, click the formula bar, and press Ctrl+Shift+Enter in older versions. In dynamic array versions, ensure the original cell contains at least one numeric character.
