How to Remove Characters from the Beginning or End of Text in Excel
Question details
The user needs to remove a fixed number of characters from either the start or the end of a text string using Excel formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up text data where unwanted characters (like ID prefixes or suffixes) consistently appear at the start or end of the cell value.
- Observed behavior
- The user wants to extract only the necessary middle or end portions of the text dynamically, without hardcoding exact string lengths for each individual row.
Identify exactly how many characters need to be removed from the start or end of your text, and ensure your data follows a consistent pattern across the rows you intend to modify.
Remove Characters from the Beginning of Text
Use a combination of the RIGHT and LEN functions, or the MID function, to bypass the first few characters of a cell.
If you want to remove a set number of characters from the start of a string (for example, the first 3 characters), you can calculate the total length of the text and extract only the remaining characters from the right side. Alternatively, you can use the MID function to start extracting from the 4th character onwards.
Click on a blank cell where you want the cleaned text to be displayed (e.g., cell B1 if your original text is in A1).
Type the formula =RIGHT(A1, LEN(A1)-3) to remove the first 3 characters, or use the MID equivalent =MID(A1, 4, LEN(A1)). Press Enter to apply.
Click the cell with the new formula, hover over the bottom-right corner until the cursor turns into a crosshair, and drag the fill handle down to apply it to the rest of your data.
Remove Characters from Both the Beginning and End
Combine the MID and LEN functions to simultaneously trim a fixed number of characters from both sides of the text.
Alternative: Use MID with an Arbitrary Large Number
A quicker, less strict method to remove starting characters when the maximum string length is known to be small.
Easily Clean Up Text Data with WPS Spreadsheet
WPS Spreadsheet fully supports essential text functions like MID, RIGHT, LEFT, and LEN, allowing you to manipulate strings and clean your data efficiently without needing complex macros. It's a lightweight, fully compatible solution for all your data formatting needs.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing the text you need to modify.
- 2. Select the target cell: Click on a blank cell next to the text you want to edit.
- 3. Apply the text formula: Type your chosen formula, such as =MID(A1, 4, LEN(A1)), and press Enter to instantly remove the unwanted characters.
- 4. Batch process the data: Drag the fill handle down the column to apply the exact same character removal logic to all selected rows.
Frequently Asked Questions
How do I remove the last 3 characters from a text string?
To remove characters strictly from the end of a string, you can use the LEFT and LEN functions together. For example, entering =LEFT(A1, LEN(A1)-3) will extract everything in the cell except the final three characters.
Why am I getting a #VALUE! error when using the MID and LEN functions?
A #VALUE! error typically occurs if the 'start_num' or 'num_chars' argument evaluates to less than zero. This happens if the total length of the text in the cell is shorter than the number of characters you are subtracting with the LEN function. Ensure your text is longer than the amount being removed.
Can I remove characters using Find and Replace instead of formulas?
Yes, if the exact characters or words you want to remove are identical across all cells (e.g., everyone has the prefix 'ID-'), you can press Ctrl+H to open Find and Replace, enter the specific text in the 'Find what' field, leave 'Replace with' blank, and click 'Replace All'.
Does the REPLACE function work for removing text at the beginning?
Yes. The REPLACE function can substitute part of a text string with an empty string. For example, =REPLACE(A1, 1, 3, "") will start at the 1st character, select 3 characters, and replace them with nothing, effectively removing the first three characters.
