Function Problems

How to Remove Prefixes and File Extensions from Excel Text in WPS Office

Nimra MalikNimra Malik Nov 13, 2026 870 views

Question details

The user needs to clean up many Excel cells by removing unwanted prefixes, extra spacing, and the .pdf file extension from each text value.

How to Remove Prefixes and File Extensions from Excel Text in WPS Office
Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
The user has a worksheet containing many text values that include a prefix, extra spaces, and a .pdf extension.
Observed behavior
Cells display the full original text including the prefix, spacing, and .pdf extension; the user wants only the cleaned filename text.
Before you start

Open your workbook in WPS Office Spreadsheets and check whether all text values share the same character positions. If the prefix and suffix lengths are fixed, use a MID formula; if values vary, use Text to Columns with a period delimiter.

Solution 1Recommended

Remove Fixed Prefixes and Extensions with a MID Formula

Use this method when the prefix and the .pdf suffix have known, fixed character positions; it is the fastest way to clean many rows at once.

The MID function returns the middle portion of a text string. In a formula such as =MID(A1,14,LEN(A1)-17), 14 is the starting position and LEN(A1)-17 calculates how many characters to keep after removing the prefix and .pdf suffix.

1
Select a result cell

Click the cell to the right of your first data cell, such as B1, in your WPS Office Spreadsheets worksheet.

2
Enter the MID formula

Type =MID(A1,14,LEN(A1)-17) and press Enter. Replace A1 with your first data cell, 14 with the position where the desired text starts, and 17 with the total number of characters to remove.

3
Fill the formula down

Use the fill handle at the lower-right corner of the formula cell and drag down to apply the formula to all remaining rows.

4
Paste the results as values

Select the formula results, press Ctrl+C, then on the Home tab open the paste dropdown and choose Values to replace the formulas with plain text.

Remove Fixed Prefixes and Extensions with a MID Formula
Cleaned text ready: The target cells now show only the cleaned text without the unwanted prefix, extra spacing, or .pdf extension.
WPS Spreadsheets Solution

Clean Excel Text in Bulk with WPS Office Spreadsheets

WPS Office Spreadsheets lets you remove prefixes, extra spaces, and .pdf extensions from multiple Excel cells quickly using built-in formulas and Text to Columns.

  1. 1. Open WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the Excel workbook containing the text you want to clean.
  2. 2. Apply the cleanup method: Use the MID formula with fill down or Data > Text to Columns to remove prefixes and .pdf extensions from the selected cells.
  3. 3. Save in Excel format: Click Menu > Save As and select Excel Workbook (*.xlsx) to keep the cleaned workbook compatible with Microsoft Excel.
  4. 4. Share or export the results: Share the file directly from WPS Office or export it to PDF if you only need to distribute the cleaned text values.
Quickly clean thousands of Excel cells without manual editingFull compatibility with Microsoft Excel .xlsx and .xls filesFree and lightweight Office suite with a familiar ribbon interfaceSimple bulk text cleanup with formulas and Text to Columns
microsoft office alternative - wps office

Frequently Asked Questions

Can I remove a prefix and .pdf extension from hundreds of rows at once?

Yes. If the prefix and extension lengths are fixed, enter a MID formula such as =MID(A1,14,LEN(A1)-17) and fill it down. Otherwise use Data > Text to Columns with fixed-width or period delimiter settings.

What should I do if my text values have different lengths?

Use Data > Text to Columns. Choose Fixed width to remove a fixed prefix, then run it again with Delimited > Other and type a period . to split the .pdf extension.

How do I keep formula results as plain text?

Select the resulting cells, press Ctrl+C, open the paste dropdown on the Home tab, and choose Values to replace formulas with the cleaned text.

Does this work in WPS Office when the workbook was created in Microsoft Excel?

Yes. WPS Office Spreadsheets opens .xlsx and .xls files and supports both MID and Text to Columns, so you can clean Excel text without converting the file.

Why does Text to Columns split my text at every period?

When you use a period as a delimiter, every period creates a new column. If your filenames contain multiple periods, merge or delete the extra columns after splitting.