How to Remove Leading Quotes and Punctuation in Excel Text
Question details
The user needs to remove leading double quotes and other unwanted punctuation marks from text strings in Excel to keep only the alphabetical letters.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up imported or improperly formatted text data where words have unnecessary leading quotes or trailing punctuation, such as changing "In, "See, and "The to In, See, and The.
- Observed behavior
- Text cells contain unwanted punctuation and double quotes alongside the actual alphabetical text, preventing accurate data analysis or clean presentation.
Before applying the formulas, insert a new blank column next to your text data to hold the cleaned results without overwriting the original cells.
Use TEXTJOIN and SEQUENCE Formulas to Extract Only Letters
This advanced array formula dynamically filters out all punctuation marks and numbers, extracting only alphabetical letters from the cell.
This method evaluates every single character in the cell and rebuilds the string using only the characters that match the defined alphabet. It is highly effective when text has multiple randomized punctuation marks.
Click on a blank cell (e.g., B1) directly adjacent to the text you want to clean.
Enter the formula: =TEXTJOIN("",,IF(ISNUMBER(SEARCH(MID(A1,SEQUENCE(LEN(A1)),1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")),MID(A1,SEQUENCE(LEN(A1)),1),""))
Press Enter to apply the formula. This will strip all characters except the specified alphabetical letters.
Drag the fill handle at the bottom-right corner of the cell down to apply this formula to the rest of your data column.
Use the SUBSTITUTE Function for Specific Characters
If you only need to remove a specific punctuation mark, such as a leading double quote or comma, the SUBSTITUTE function provides a simpler alternative.
Easily Clean Up Text Data in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like TEXTJOIN and SEQUENCE, making data cleanup tasks like removing quotes and punctuation quick and efficient.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the text data you need to clean.
- 2. Select a blank cell: Click on the cell where you want the cleaned text string to be displayed.
- 3. Input the formula: Type or paste your preferred TEXTJOIN or SUBSTITUTE formula to target the unwanted punctuation.
- 4. Batch process data: Press Enter, then drag the fill handle down to instantly clean all the text entries in that column.
Frequently Asked Questions
Can I use Find and Replace to remove leading double quotes?
Yes. You can press Ctrl + H to open the Find and Replace dialog, type a single double quote (") in the 'Find what' field, leave the 'Replace with' field completely blank, and click 'Replace All'.
Why does my TEXTJOIN formula return a #NAME? error?
This error usually occurs if your version of the spreadsheet software does not support the SEQUENCE or TEXTJOIN functions. You will need to upgrade to a newer version of Excel or WPS Office.
How do I remove all punctuation but keep both letters and numbers?
You can expand the search string in the TEXTJOIN array formula to include numbers (0-9) alongside the alphabet letters. This tells the formula to retain both numbers and letters while stripping all other symbols.
