How to Split Excel Data into Separate Sheets by First Three Characters in WPS Office
Question details
The user needs to group rows in a worksheet by the first three characters of a text column and automatically copy each matching group to a separate worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A source column contains text values that must be grouped according to their first three characters, with each group distributed to its own sheet.
- Observed behavior
- Matching records remain in the source sheet and formulas cannot write results to other worksheets, so the task requires either a VBA macro or a manual filter-and-copy workflow.
Before running the macro, confirm the source sheet name and the source column match the code, and save the workbook as a macro-enabled file (.xlsm) so the VBA project is retained.
Use a VBA Macro to Automatically Create a Sheet for Each Prefix and Copy Matching Rows
Use this solution when you need the source rows automatically distributed to multiple worksheets without manual filtering and copying.
VBA is required when the result must be written to multiple worksheets automatically because Excel formulas only return values in the cells that contain them. The macro reads the source column, extracts each value's first three characters with Left, creates or finds a matching worksheet, and copies the record to that sheet.
In WPS Spreadsheets, press Alt+F11, or click the Developer tab and then click View Code.
In the VBA editor, click Insert > Module to create a new code module.
Paste a VBA macro that loops through column A from A2 to the last used row, extracts Left(cell.Value, 3), creates a worksheet named with that prefix if it does not exist, and copies the entire row to the matching worksheet.
Change the source worksheet name and column reference in the code to match your workbook, for example Sheet1 and column A.
Place the cursor inside the macro and press F5 or click Run > Run Sub/UserForm, then save the workbook as a macro-enabled file (.xlsm).
Create a Helper Column with LEFT and Filter Each Prefix Group
Use this formula-only approach when you prefer not to run macros and have a manageable number of groups to copy manually.
Split Excel Data into Separate Sheets by First Three Characters with WPS Office
WPS Spreadsheets can run the VBA macro directly, automatically creating a worksheet for each three-character prefix and copying matching rows. It keeps your Excel-compatible workflow intact and supports both .xlsx and .xlsm files.
- 1. Open your workbook: Open the target workbook in WPS Spreadsheets.
- 2. Open the macro editor: Press Alt+F11 or click Developer > View Code, then insert a new module.
- 3. Add the split-by-prefix macro: Paste the VBA macro and change the source sheet name and source column to match your data.
- 4. Run the macro: Press F5 or click Run > Run Sub/UserForm to create and populate a worksheet for each three-character prefix.
- 5. Save as macro-enabled workbook: Save the file as .xlsm so the macro is stored and can be reused.
Frequently Asked Questions
Can I use this method to group by the first 4 or 5 characters instead of 3?
Yes. In the VBA macro, change Left(cell.Value, 3) to Left(cell.Value, 4) or the number you need, and replace the helper-column formula =LEFT(A2,3) with =LEFT(A2,4).
Why does the macro fail with an invalid sheet name error?
Usually one of the first three characters is not allowed in a worksheet name, such as \, /, *, ?, :, [ or ]. Clean or replace those characters before naming the destination sheet.
Do I have to save the workbook as a macro-enabled file?
Yes. Save it as .xlsm in WPS Spreadsheets or Microsoft Excel, otherwise the VBA macro will not be stored when you close the file.
Can I split the data without enabling macros?
Yes. Add a helper column with =LEFT(A2,3), turn on AutoFilter, filter each prefix, and copy the visible rows to separate sheets manually.
